whats the SQL query for this?

[nabeels786]

i have a table with a bunch of colums

one of the colums is called "gamename", another is "id"

when the user clicks on a letter, i want it to sort the list by the gamename, for the names that start with the letter that the user chooses, and it links to it's id, in alphabetical order

but there is another column called "system", and i want it to be in groups

LETTER H
   PC GAMES
     HALF LIFE

   XBOX
     HALO

the table name is "reviews"

like you see what i mean? the other columns are "review" and etc,
i want it to link to <ahref="viewreview.php?rid=<id>

but, that isnt my problem, its the SQL query. if i use multiple queries, thats all good too (whichi guess will have to be used)

Thanks a ton!

[chrisjk]

To do the first bit, you can do this

SELECT * FROM reviews WHERE left(gamename, 1) = 'H' ORDER BY gamename

as for putting it into groups, you'll have to perform that query for each group, adding another part to the WHERE clause AFAIK

[nabeels786]

so could i do


SELECT * FROM reviews WHERE left(gamename, 1) = 'H' ORDER BY gamename AND system = "pcgames" ?

or does the AND go after the 'H'?

thanks

[chrisjk]

yep, after the h, like this

SELECT * FROM reviews WHERE left(gamename, 1) = 'H' AND system = 'pcgames' ORDER BY gamename

[nabeels786]

ok cool thanks

look at this other tutorial thing, how does this look?

$sql="SELECT id,gamename, system FROM reviews WHERE left(gamename, 1) = 'H' AND system = 'pcgames' ORDER BY gamename"

$result = mysql_query($sql);

while ($row = mysql_fetch_row($result)) {
    printf("<li><a href=\"?viewreview.php?id=%s\">%s<\a><\li>", $row[0], $row[1]);
} 

Does it need "system" in "SELECT id,gamename,system"?

[chrisjk]

no, you don't have to put system in the SELECT statement. You only need it there if you want to use the value in something

As a side note, you might want to use mysql_fetch_array instead of mysql_fetch_row because then you can refer directly to the field names

$row["gamename"] 

[nabeels786]

so itll be

while ($row = mysql_fetch_array($result)) {
    printf("<li><a href=\"?viewreview.php?id=%s\">%s<a><li>", $row["id"], $row["gamename"]);
} 

heh, sorry im new to this stuff

thanks tho

[chrisjk]

that's it

[nabeels786]

cool

thanks alot.


also, is there a way i can do it for all of the systems in once query, instead of multiple queries? prolly getting outta my knowledge but eh

[chrisjk]

not if you want to format the page like you described because you'd get all the results at once. Ideally you want them in blocks according to their system and that is practically the only way to do it.

mysql is very fast anyway so you wouldn't notice the difference

[nabeels786]

ah ok. alrighty then

thanks again