...

View Full Version : Displaying names into alphabetical groups - help!



eapro
09-16-2009, 08:47 AM
I ran into yet another tricky case while developing a site. Here is the problem...

I have a mysql table with a bunch of authors' names. I would like to show 5 random names grouped separately by the first letter of the name.

I solved the problem by doing a query for each alphabet (26 queries!) like this (cleaned up a bit for forum):


foreach($letters as $letter){

$query = mysql_query("SELECT name FROM authors WHERE
name LIKE('".$letter."%') ORDER BY RAND() limit 5");

echo "<h1>$letter</h1>";

while($row = mysql_fetch_assoc($query)) {
echo "<a href='#'>{$row['name']}</a><br>";
}

}

Indeed this method worked, but as you can imagine, it slows down the webpage quite a bit.

My idea is to do just one query to get all names, then put the results into a multidimensional array, with an index for each alphabet. However, I'm struggling with how to implement this idea.

http://img32.imageshack.us/img32/3387/authorlist.th.png (http://img32.imageshack.us/i/authorlist.png/)

mlseim
09-16-2009, 02:03 PM
It will be something like this ...
although I didn't test it.

$query = mysql_query("
SELECT case when
SUBSTRING(name from 1 for 1) between '0' and '9' then
' number' else SUBSTRING(name from 1 for 1) end as letter,
count(name) AS occurrences
FROM authors
GROUP BY letter
ORDER BY occurrences RAND() limit 5
");

Look for more possible examples here:
http://www.google.com/#hl=en&q=mysql+group+by+first+letter&aq=f&aqi=&aq=0&aqi=g1&oq=mysql+group+by+first+let&fp=ee36edbd3c16a1c5



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum