Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Feb 2007
    Thanked 1 Time in 1 Post

    Displaying names into alphabetical groups - help!

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

    PHP Code:
    foreach($letters as $letter){

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


    $row mysql_fetch_assoc($query)) {
    "<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.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Cottage Grove, Minnesota
    Thanked 1,093 Times in 1,084 Posts
    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:


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts