Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    113
    Thanks
    6
    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");

        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.


  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,468
    Thanks
    8
    Thanked 1,085 Times in 1,076 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:
    http://www.google.com/#hl=en&q=mysql...36edbd3c16a1c5


  •  

    Posting Permissions

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