SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    alphabatizing with a twist

    Hey guys.

    I am creating a database of credential holders. I want to provide a nav with the letters of the alphabet A-Z but only include the letters that have entries under them. IOW, I have a table like this (example):
    Code:
    +-----------+
    |name       |
    +-----------|
    |Abigail    |
    |Amber      |
    |Hank       |
    |Greg       |
    |Tia        |
    +-----------+
    Opening the page should run a query that displays a alphabetical nav bar like this:
    Code:
    Select Letter to search by: A  G  H  T
    Any ideas?

    Aaron
    Aaron Brazell
    Technosailor



  2. #2
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this query to get the first letters of all of the records.

    SELECT LEFT(name, 1) as firstletter FROM table;

    Then, do something like this...
    Code:
    for each result
       letters[$firstletter]++
    
    for each key in letters[]
       echo a link to sort by the letter
    Last edited by qslack; Aug 24, 2002 at 13:38.

  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well since I'm using PHP and I'm Perl-stupid, the last part won't work but I can figure all that out no problem. It was the query that wqs killing me. You are almost right on the query. Here's the working query:
    Code:
    SELECT DISTINCT LEFT( lname, 1 )
      as firstletter
    FROM directory
    ORDER BY firstletter
    Thanks Quinn!
    Aaron
    Aaron Brazell
    Technosailor



  4. #4
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, that was pseudocode. So you should be able to translate everything to PHP pretty easily.

    I guess I forgot about DISTINCT and the alphabetization of the results. Glad you got everything working!


Bookmarks

Posting Permissions

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