Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    1,471

    PHP/MySQL Alphabetical List with Headings

    I'm trying to do something like this, a database holds lets say the names of various music albums and I want to display them on a page alphabetically with headings for each letter of the alphabet.

    A
    -----
    A Better Something
    Alpha Something
    Already Blah

    B
    -----
    By the Way
    Better Than Carp

    (you get the idea)

    The part I'm lost on is how to separate out stuff like @Name or !Test!, I would like to sort out all the stuff that doesn't start with a number or a letter of the alphabet. Not sure how I would do that with SQL. Any help would be appreciated.

    Also, is the the best way of pulling out all entries starting with the letter A:
    PHP Code:
    "SELECT name FROM albums WHERE name LIKE 'A%' ORDER BY name ASC" 
    Thanks!

  2. #2
    can you explain what the @name and !test! is needed for, im kinda lost as to why those need to be in your database.

  3. #3
    Join Date
    Jun 2008
    Posts
    1,471
    Those are examples of stuff that does not start with an alphanumberic (A-Z). I need to get everything that does not start with an alphanumeric out of the database and display it in a special heading.

  4. #4
    Join Date
    Jun 2008
    Posts
    1,471
    Example:

    #
    ------
    !ROck!
    %Album Name*
    #30 Rock

    See how all those dont start with an alphanumeric, how do I query all of those out of the database easily?

    Thanks in advance.

  5. #5
    Join Date
    Aug 2003
    Location
    California, USA
    Posts
    582
    PHP Code:
    <?php
    $a_z 
    range('A','Z');
    $query "SELECT name FROM albums WHERE upper(name) ";

    foreach (
    $a_z as $letter)
    {
        
    $query .= "NOT LIKE '"$letter "%'";
        if (
    $letter != 'Z')
            
    $query .= ' AND upper(name) ';
    }
    $query .= " ORDER BY name ASC"
    ?>
    Last edited by etogre; 08-25-2008 at 08:05 PM.

  6. #6
    This should also work for what you want

    SELECT * from music WHERE ord(name) < 65 || ord(name) > 90 and ord(name) < 97 || ord(name)>122

  7. #7
    Join Date
    Nov 2005
    Location
    Egypt.
    Posts
    76
    Man, you should think about "Group By", here's a tutorial about it :
    http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

  8. #8
    Join Date
    Jun 2008
    Posts
    1,471
    Quote Originally Posted by healthnut101 View Post
    This should also work for what you want

    SELECT * from music WHERE ord(name) < 65 || ord(name) > 90 and ord(name) < 97 || ord(name)>122
    Thanks, works great. Just changed it to exclude numbers as well
    SELECT * from msuic WHERE ord(name) < 48 || ord(name) > 57 AND ord(name) < 65 || ord(name) > 90 AND ord(name) < 97 || ord(name) > 122

  9. #9
    Join Date
    Nov 2005
    Posts
    123
    Here's another version (to include A-Z/a-z/0-9), with some test code:
    PHP Code:
    $qry "SELECT name, LEFT(name, 1) AS first_char FROM albums 
            WHERE UPPER(name) BETWEEN 'A' AND 'Z'
            OR name BETWEEN '0' AND '9' ORDER BY name"
    ;
     
    $result mysql_query($qry);
    $current_char '';
    while (
    $row mysql_fetch_assoc($result)) {
        if (
    $row['first_char'] != $current_char) {
            
    $current_char $row['first_char'];
            echo 
    '<br />' strtoupper($current_char) . '<br />-----<br />';
        }
        echo 
    $row['name'] . '<br />';

    Getting first_char isn't really necessary, but it makes the PHP code a little simpler.
    Last edited by bigfan; 08-26-2008 at 02:12 AM.

  10. #10
    Join Date
    Nov 2005
    Posts
    123
    Sorry, I screwed that up when I tried to edit it.
    PHP Code:
    $qry "SELECT name, LEFT(name, 1) AS first_char FROM albums 
            WHERE UPPER(LEFT(name, 1)) BETWEEN 'A' AND 'Z'
            OR LEFT(name, 1) BETWEEN '0' AND '9' ORDER BY name"


Posting Permissions

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