www.webdeveloper.com
Results 1 to 13 of 13

Thread: PHP/MySQL Alphabetical Grouping

  1. #1
    Join Date
    Jan 2003
    Location
    Cardiff, Wales, UK
    Posts
    208

    PHP/MySQL Alphabetical Grouping

    I'm creating a glossary page that has the letters of the alphabet listed across the top, each being a bookmark that takes you to the relevant letter section on the page. If there are no entries for a letter in the DB, I don't want the letter to be a link.

    At the moment, i do 26 SELECT LIKE statements (e.g. 'a%', 'b%', etc.) - and it works, but it's grossly ineffcient.

    Can I do this in a single MySQL statement and then do the processing in PHP? I'm assuming there is some kind of GROUP BY syntax I can use?

  2. #2
    Join Date
    Feb 2003
    Location
    Britain
    Posts
    1,335
    You can order things when you get them with the mysql query by adding
    ORDER BY fieldname
    to the query.

  3. #3
    Join Date
    Jan 2003
    Location
    Cardiff, Wales, UK
    Posts
    208
    I know that thanks, but is there a way of efficiently processing those results, rather than iterating through the array and doing string compares and stuff?

  4. #4
    Join Date
    Feb 2003
    Location
    Derby, UK
    Posts
    456
    Geat,

    If you mean just finding the count per letter, then something like (untested)

    SELECT substring(columnname,0,1) AS FirstLetter,count(*) As NumItems FROM table GROUP BY FirstLetter ORDER BY FirstLetter

    should work, thogh you may need to repeat the substring in the group by if it wont recognise the alias (I think it will but...)

    HTH,

    Dai

    (resident of Cardiff age 4-18 )

  5. #5
    Join Date
    Jan 2003
    Location
    Cardiff, Wales, UK
    Posts
    208
    Tidy bud, I'll give that a go!

    It was so close! Just the substring call should have been (x, 1, 1) rather than (x, 0, 1). Yup, that's the totally inconsitent world of programming for you!

    I'm the opposite of you, I've been living here since I was 18...
    Last edited by Geat; 06-03-2003 at 09:08 AM.

  6. #6
    Join Date
    Feb 2003
    Location
    Britain
    Posts
    1,335
    Maybe you two are actually one person...

  7. #7
    Join Date
    Feb 2003
    Location
    Derby, UK
    Posts
    456


    What, in a Fight Club/Angel Heart kind of way?

    No I tried growing a beard but it never looked that good so that cant be me

  8. #8
    Join Date
    Dec 2006
    Posts
    3
    I've managed to use this code successfully but I now need to take the value of FirstLetter and use it in my PHP code. I've tried searching on the web for tips but as I don't quite know what you call the FirstLetter 'variable' (a temp store?) it's making it a little difficult! Please can someone tell me how i would use this in PHP code or what I need to search on the web - I'm using dial up so it takes quite a while!

    thanks
    JM

    PS Lived in Cardiff from age 19-22yrs!

  9. #9
    Join Date
    Feb 2003
    Location
    Derby, UK
    Posts
    456
    Assuming you have something like

    $rstLetters = mysql_query('SELECT substring(columnname,1,1) AS FirstLetter,count(*) As NumItems FROM table GROUP BY FirstLetter ORDER BY FirstLetter');
    while($arrLetters = mysql_fetch_array($rstLetters)) {
    .......

    then the first letter is in $arrLetters['FirstLetter'] within the while loop and FirstLetter in this scenario is called an "alias".

    HTH,

    Dai

  10. #10
    Join Date
    Dec 2006
    Posts
    3
    Thanks Dai.
    However, I don't completely understand how to fit this in with what I want to do. (bit of a PHP newbie!)

    I am trying to create an alphabetical search facility for a music catalogue. So far, I have a page which outputs the first letter of all the artists in the database as a hyperlink. I then want them to be able to click on the hyperlinked letter which will then display all artists which start with that letter. I am a bit stuck on how to create the SQL query to do this using the alias. I've attached the code I have written so far - pls can you take a little look and tell me where I am going wrong!

    Thanks
    JM
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2003
    Location
    Derby, UK
    Posts
    456
    JM,

    The main problem is that you are using

    $query = "select DISTINCT artist
    from MUSIC where substring(artist,1,1) = '$FirstLetter'";

    but $FirstLetter is not set anywhere. if you add this line

    $FirstLetter = $_GET['catid'];

    before the query, this will ensure that the letter is picked up from the URL parameter (e.g. ?catid=A would yield $FirstLetter = 'A').

    Note that for production code there are a number of issues to consider:
    • You should validate all user input (I haven't in my simple example)
    • You should check errors after all database queries
    • functions that you use in both pages should be included in from a common file
    • etc.


    but I have just focused on what you need to get the code working.

    Regards,

    Dai

  12. #12
    Join Date
    Dec 2006
    Posts
    3
    Woot! That works, thanks v much Dai!

    Taken on board your comments, will look into this once main functionality is built. :-)

    Thanks again.
    JM

  13. #13
    Join Date
    Feb 2003
    Location
    Derby, UK
    Posts
    456
    np, glad to help

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles