PHP & MySQL Tips: AZ List from DB

Past two days I have been busy designing a website! It is not too fancy from outside, but as usual all my websites are purely server based, with PHP and MySQL in the back end! So here is a tip for those PHP and MySQL fans! So if you are experienced in MySQL & PHP this can save you some time.

In a dynamic site AZ list can be tricky, as the users are allowed to add and edit the content of the website the AZ list contents can change at anytime.

azlist.jpg

In addition I don’t like to have links to pages which will have no contents, in other words if there is no resort starting with ‘U’, why have a link to it!

So here is the MySQL query I used:

“SELECT DISTINCT UPPER(LEFT(LName,1)) as letters FROM resorts ORDER BY letters”;

This is a simple query, here ‘LName’ is the name of the field and ‘resorts’ is the table in the database. So you will need a table named ‘resorts’ with at least one field names ‘LName’. So substitute as needed. This query will give you a list of unique first letters of all the data in the given fields!

azlist02.jpg

Now all you have to do is to loop the query result using PHP! Here is the complete coding!

//Database Connection String

$hostname = “server_localhost”;
$database = “database_name”;
$username = “user_name”;
$password = “password”;
$connection1 = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);

//Data Extraction

mysql_select_db($database, $connection1);
$query_AZList = “SELECT DISTINCT UPPER(LEFT(LName,1)) as letters FROM resorts ORDER BY letters”;
$AZList = mysql_query($query_AZList, $connection1) or die(mysql_error());
$row_AZList = mysql_fetch_assoc($AZList);
$totalRows_AZList = mysql_num_rows($AZList);

// Displaying the result by looping using PHP

<?php do { ?>
<?php echo ‘<a href=”list.php?ltr=’.$row_AZList[‘letters’].'”>’.$row_AZList[‘letters’].'</a>’; ?>
<?php } while ($row_AZList = mysql_fetch_assoc($AZList)); ?>

Thats it! Hope this will come in use for someone some day!

Advertisements
  1. #1 by Inash Zubair on July 2, 2007 - 7:29 am

    It sure will. This will be appreciated by the web development community. Cheers!

  2. #2 by Corey on September 21, 2007 - 7:00 am

    Thanks for this. Just the idea I was looking for!

    My personal preference is to display all letters A-Z and only link the ones from the query result. I just think an alphabetical index doesn’t look right if letters are missing.

    I just dumped the results into a string and looped through an A-Z array looking for matches in the string. Probably a better way of doing it, but this is what I came up with…

    $query = “SELECT DISTINCT UPPER(LEFT(Lname,1)) as letters FROM $table ORDER BY letters”;

    if ($result = mysql_query($query)) {
    while ($row = mysql_fetch_array($result)) {
    $letter_string .= $row[“letters”];
    }
    }

    $letter_array = range(‘A’,’Z’);

    foreach ($letter_array as $letter) {

    if (stristr($letter_string, $letter) === FALSE) echo $letter . ” | “;
    else echo ““.$letter.” | “;

    }

  3. #3 by mapii on November 22, 2010 - 1:30 pm

    What abou 0-9 listins?

  4. #4 by frenky on May 8, 2011 - 7:37 am

  5. #5 by Tfujisdq on September 24, 2011 - 10:27 pm

    A First Class stamp Silver Teen Model
    32382

  6. #6 by Zktvwysc on September 26, 2011 - 5:47 am

    What sort of work do you do? Kds Models sowq

  7. #7 by Muzafar on October 28, 2011 - 3:10 am

    Thanks for the giving me concept i was looking for 🙂

  8. #8 by gutscheingigantde on May 2, 2012 - 7:17 pm

    Not working…Full of syntax errors

  9. #9 by Zzkoestp on May 7, 2012 - 4:10 pm

    I’d like to change some money http://efycelicakalu.de.tl anya lsmodels eso es culiar yo me culia a la mama de mi novia y casi me lo parte lo q no sabe la lo sabe la mama y culea rico

  10. #10 by Developement Information on May 6, 2013 - 7:02 pm

    Terrific article! This is the kind of information
    that are supposed to be shared around the internet.
    Disgrace on Google for no longer positioning this post higher!
    Come on over and consult with my website . Thank you =)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: