Easy Beginner Question

[samurai]

I'm relatively new to PHP and entirely new to mySQL.

I have a database named "quotes" setup with about 50 quotes, with three fields: ID, quote, and author.

ID is the numerical ID number, quote is the actual quote itself, and author is the person who said the quote.

I am putting together a page that lists all of the quotes in the database catagorized by author (there are only about three different "authors" throughout the 50 quotes).

How do I go about searching the database for any row where author = "a" and printing out the quote field in that row?

[raf]

Please use a meaningfill title http://www.codingforum.net/postguide.htm

Where you say database, do you then mean 'table'?

if so, then your select will be

SELECT quote FROM quotes WHERE author = 'a'

To print them out, you'll have

// open connection and select db
$select = "SELECT quote FROM quotes WHERE author = 'a'";
$result = mysql_select($select) or die ('Queryproblem');
if (mysql_num_rows($result) >= 1){
   while ($row=mysql_fetch_assoc($result)){
       echo '<br />' . $row['quote'];
   }  
} else {
   echo 'No records found.';
} 
mysql_free_result($result); 

Note : the 'a' shoudl better be an integer because db's search a lott quicker on numerical data (as compaired to strings)

[samurai]

Thanks raf. I apologize for the vague thread title, I'll change it to make it more descript. There were some others of similar nature floating around, so I assumed it was acceptable.

The 'a' is not an integer at the moment, but instead a string. However I can easily add a new field named 'authnum' and assign each author a smallint and search that field instead.

Thanks again, I'll give it a try and let you know how it turns out.

[javaPete]

Originally posted by samurai
The 'a' is not an integer at the moment, but instead a string. However I can easily add a new field named 'authnum' and assign each author a smallint and search that field instead.

hope u dont mind me butting in but seems a little strange to change the database structure based specific type of search. Am i to assume you would create number column 1-26 to represent what letter out of the alphabet the author begins with?

Author tbl
aID SORT NAME
01 02 bob
02 02 betty
03 26 zoe

Quote tbl
qID aID QUOTE
01 02 blah bha

[raf]

<<<<hope u dont mind me butting in but seems a little strange to change the database structure based specific type of search.>>>>
When i said search, then i mean a search of the RDBM (Relational DataBase Manager) to filter out the records with a specific value. Filtering simply goes faster on numerical columntypes, like smallint's, integers etc. If at all possible, then all your keys (fields you use inside a join or frequently use in a where-clause) should be numerical columntypes and should be indexed, the enhance performance further.
<<<<Am i to assume you would create number column 1-26 to represent what letter out of the alphabet the author begins with?>>>> I don't understand that. No need to create a seperate column for the first letter of the name, since LEFT(author,1) also returns that (if you need it. The classical db settup is

An authortable (authors):
autherID | authorName | other authervars.?
1 | Bill |...?
2 | Raf | ...?
3 |Anton | ...7
...
autherID is a autonumbered primary key

A quotestable (quotes):
quoteID | quote | autherID
1 | 'No mole enjoys the snow' | 2
2 | 'Never trust an empty bottle'|1
...
autherID is a foreign key --> non-unique index that is used to join these two tables
quoteID is here the PK.

To get all records of author who's name start with an A, you'd have:
SELECT quotes.quote, authors.authorName FORM authors INNER JOIN quotes ON authors.autherID = quotes.autherID WHERE authorName LIKE 'a%'

[javaPete]

maybe were just arguing over semantics, but what ur describing database normalization (spliting large tables into smaller ones and removing repetative data) and then joining the tables on a numeric id column. You still have to do the search on the authorName column which is a string type, so it's incorrect to say the search is faster because you're seaching an integer column type.

WHERE authorName LIKE 'a%'

[raf]

I don't know what your point is.

He was initially filtering on the the value of author. In his example "a". Not "%a" or "a%" or "%a%" or whatever. You made that up.

My note on this is:

Note : the 'a' shoudl better be an integer because db's search a lott quicker on numerical data (as compaired to strings)

Which is absolutely correct. And this is generaly called filtering, not searching. And certainy if the variable is indexed, what would be normal in such a design, then there is a big difference between filtering and searching, since filtering will only use the indexes to look up the relevant datapages, while a search will process all actual values of the records.

Columns you frequently use for filtering should be integers, if possible. And for big table, it's even recommended to create a dimensiontable just to be able to use a (foreign key) index to do the filtering on. Your post is just confusing but i fail to see your point

Your initial remark is completely beside the point here

hope u dont mind me butting in but seems a little strange to change the database structure based specific type of search. Am i to assume you would create number column 1-26 to represent what letter out of the alphabet the author begins with?

. I though the answer was cler : no you don't. You misunderstood the suggestion, probably because you invented the 'match first letter' requirement.
And i still don't see why you would need a seperate column for that since there are better ways to get the same result. (as i tried to axplain in my reply)

In my second reply, i just used that query to show how a simple normalised design is also capable of selecting on the first letter. I was not replying to his initial post. I was replying to your post.

For his initial post he would just need

SELECT quotes.quote, authors.authorName FORM authors INNER JOIN quotes ON authors.autherID = quotes.autherID WHERE authors.autherID =1

The query i wrote was to demonstrate their is no need for your extra column with a numerical representation for the first letter.

And this is not only for performance reasons. A relational design is harly ever faster then flatfile tables. The other advantages are less dataredundancy, less updating required, clearer and logical structure.

[javaPete]

sorry dude, misread his initial post, when i saw his sql statement with 'a' i assumed he wanted to return quotes from all authors whose name started with a. So when you suggested searching/filtering on the author id, i was like thats impossible.

btw, eventhough I'm new to mysql I've worked with databases for many years and know normalization like the back of my hand so don't talk down to me.

[raf]

Originally posted by javaPete
btw, eventhough I'm new to mysql I've worked with databases for many years and know normalization like the back of my hand so don't talk down to me.

Please show me where i was talking down to you. I could say just the same, dude. Thats just a bit to easy.

You first make some comfusing incorrect posts with a real strange sollution to a nonexisting problem (i wonder if someone with years of experience with db's would ever come up with something like that )and now your complaining about what exactly? That i explained it to simple and elaborat for such an experience db-user as you are? You didn't exactly show much expertise, and this thread isn't only intended for you --> less experienced users will get quite comfused about your posts and the things you tough you needed to set straight.

So i took the time to streighten it out. Are is it not allowed to point out your misconceptions?

I obviously shouldn't have bothered.