MySQL A to Z listing

[Tewl]

I'm trying to list data in mysql A-Z and i know how to do it if I make an query for each individual letter. My only idea so far would be to set the a variable for the letter than do an if statement to tell which letter to change it to but its kinda sloppy coding. Does anyone have any better ideas that might could help me out? thanks

[nemonoman]

Well, I'll only say the obvious in order to find out what your problem REALLY is -- what's wrotng with ORDER BY? as in

SELECT somecol FROM somtable
WHERE something=somethingelse
ORDER BY somecol

Which would get you somecol sorted A-Z

Now what did you REALLY want?

[planetsim]

errrr very close but you forgot to add asc at the end

[Tewl]

Well more specifically my listing will be something like the following:

Matches for A

-a 1
-a 2
-a 3

Matches for B

-No results found for b

Matches for C

-c 1

ect... all the way to z

right now im doing

select * from table_name where LEFT(name,1)='A';

for each letter

which makes it a lot of code

[nemonoman]

just a note to planetsim that ASC is the default for an ORDER BY, at least in MySQL

[nemonoman]

About your leftjoin approach, I suggest you think of this as a display question, not a query.

I'd do this in PHP rather than in SQL:

Get the results ORDER BY the alpha column, then write a loop to test the result arrays against the alphabet (you can do this by chr($integer)), displaying either a blank letter, or the query results.

For example:

$charnum=65;
while($answer=mysql_fetch_array($someresult)&&($charnum<91)) {
if(substring ($answer[0],1,1)==chr($charnum)){
echo $answer[0];
}
else {echo chr($charnum);}
$charnum++;
}