[RESOLVED] Sorting by first letter

IronTree

I have a huge database and need to sort by first letter, basically A-Z. This is what I have so far , but it gives me ALL the results.

$result = mysql_query("SELECT Distinct id,name FROM table WHERE name REGEXP '[A-Z]' ORDER BY name  ") 
or die(mysql_error()); 


while($row = mysql_fetch_array( $result )) {

$id=($row['id']);
$name=substr($row['name'],0,1);


echo "<a href=\"page.php?id=" .$id. "&name=" .$name. "\"> $name</a>\n";
}

The result of this looks something like this, but thousands more:

AAAA
AAAA
AAAA
AAAA
BBBB
BBBB

etc..

What do I need to do to just have one of each letter and then obviously when that letter is clicked it show results for all rows in that particular letter?

Thanks.

bradgrafelman

Well it's obvious why your query doesn't work.. you're just selecting all records that begin with a letter and then sorting by their full 'name'.

What you need to do is get rid of the WHERE condition (unless you actually intended to limit the results to those that begin with a letter) and use an ORDER BY clause such as:

ORDER BY LEFT(name, 1) DESC

IronTree

bradgrafelman wrote:
Well it's obvious why your query doesn't work.. you're just selecting all records that begin with a letter and then sorting by their full 'name'.

What you need to do is get rid of the WHERE condition (unless you actually intended to limit the results to those that begin with a letter) and use an ORDER BY clause such as:
ORDER BY LEFT(name, 1) DESC

oK, thanks, actually GROUP BY is what worked. Thanks again.