Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: Create alphabetical directory in PHP off mysql database

    I'm working on my first PHP/MySQL site and have laid out the site requirements, but am having trouble figuring out how to create a members' directory page, sorted alphabetically with alpha anchors at the top of the page. The data will be taken from a MySql database (which so far only has a last name field).

    In other words if someone wants to see the people with last names starting with a "D", they need to be taken to the 4th section of the page. How can I determine the construction of this page?

    Hope that this is not just my ignorance showing....thanks for any help,

  2. #2
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Re:Create alphabetical directory in PHP off mysql database

    Its pretty simple only.
    say if your tablename is members and your fieldname is lastname..

    Then all you have to do is to execute a query
    $mysql="select lastname from members order by lastname asc";
    $cur=mysql_query($mysql);
    while($row=mysql_fetch_array($cur))
    {
    echo $row['lastname'];
    }

    this would give you a list of all the names sorted but it wouldn't group names together .For that , you would have to add your additional conditions like this


    $mysql="select lastname from members order by lastname asc";
    $cur=mysql_query($mysql);
    $previous="";
    echo "<a>";
    while($row=mysql_fetch_array($cur))
    {
    $current=substr($row['lastname],0,1);
    if($current !=$previous)
    {
    echo "</a><hr><a name=$current>";
    }
    echo $row['lastname'];
    }

    echo "</a>";


    Then where ever you want call these sections accordingly ...
    Last edited by rexselin; 11-27-04 at 06:08.

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Good Example rexselin Thanks.
    Tried it out.
    Had to add a line to update the $previous variable. Here's the updated example.

    PHP Code:
    $mysql="select lastname from members order by lastname asc";
    $cur=mysql_query($mysql);
    $previous="";
    echo 
    "<a>";
    while(
    $row=mysql_fetch_array($cur))
    {
    $current=substr($row['lastname'],0,1);
    if(
    $current != $previous)
    {
    echo 
    "</a><hr><a name=$current>";
    $previous $current ;
    }
    echo 
    $row['lastname'] . "<br>\n";
    }

    echo 
    "</a>"
    ~

    Bill

  4. #4
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    re-alphabetical sorting

    Thanks savbill ... wonder how i missed out that part .... should be testing my code hereafter before posting a reply ..

    Thanks & Regards,
    Celia

  5. #5
    Join Date
    Mar 2005
    Posts
    2
    This is almost what I'm looking for....
    I'm pretty novice using php/mysql, but I seem seem to figure out out most of my problems, except for the alphabetical thing.

    I hav a db filled with artists, and i need a way to have them ordered alphabetical, per page, eg ABCDEFG..etc, where the letters can be clicked and the query shows me only artists that start with the clicked letter.

    Anyone here willing to help me out? this thing is driving me nuts :P

  6. #6
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    This is also pretty easy.First you would need to decide on the field that determines the sort order.(First Name or LastName).
    Then perform a query, that would take out the first character of the field data.There are substring functions in mysql to do this task.You may have to give DISTINCT clause to the obtained result ; otherwise you will end with duplicate letters.

    Once you have got the alphabets, display them in a loop and link them to a page, and pass the character as a query string.In that page,grab this parameter and perform a query like this.

    select * from table where fieldname like '$char%'

    Does this answer your query? Tell me if you find any difficulty here.

    Regards,
    Celia

  7. #7
    Join Date
    Mar 2005
    Posts
    2
    Thanks a bunch!
    I figured it out, your were right, it's realy so easy, I almost feel stupid :P

    Thanks alot for your help

    -Peter

Posting Permissions

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