whats the SQL query for this?

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

Code:

---

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!

To do the first bit, you can do this

Code:

---

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

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 :)

yep, after the h, like this

Code:

---

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

---

ok cool thanks :)

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

PHP Code:

---

$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"?

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

PHP Code:

---

$row["gamename"] 


---

so itll be

PHP Code:

---

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 :D

thanks tho :)

that's it ;)

cool :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 :)

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 :cool:

ah ok. alrighty then

thanks again :)