BeWebmaster

PHP A to Z sorting script

Ad

Let's say you have a customer table ( tbl_cutomers) you want to navigate alphabetically by customer last name.

id firstname lastname
1 John Smith
2 Joanne Johnson

tbl_customers

To accomplish this task we need to create PHP script that will:

  1. get the letter user clicked on
  2. execute MySQL query based on that letter
  3. display customers whose last name starts with selected letter.

If no letter was clicked we will display all customers. Let's call that script getcustomers.php.

getcustomers.php

<? php//first we need to connect to the database
$host = 'localhost';                  //Database server LOCATION
$name = 'your database name';         //Database NAME
$username = 'your database username'; //Database USERNAME
$password = 'yourdatabase password';  //Database PASSWORD

//this makes the connection
$connection = mysql_connect($host, $username, $password) or die ('Unable to connect to the database');
mysql_select_db($name) or die ('Unable to select database!');

/* Now let's get the letter user clicked on and assign it a variable called $sort */
$sort = $_REQUEST['letter'];

/* Let's check if variable $sort is empty. If it is we will create a query to display all customers alphabetically ordered by last name. */
if($sort = ""){
$qry= "SELECT * FROM tbl_customers ORDER BY lastname ASC " ;
}else{
/* if varible $sort is not empty we will create a query that sorts out the customers by their last name, and order the selected records ascendingly. */
$qry = "SELECT * FROM tbl_customers WHERE lastname LIKE '$sort%' ORDER BY lastname ASC" ;
}
/* Notice the use of '%' wilde card in the above query  "LIKE '$sort%'". */

//next step is to execute the query.
$execute = mysql_query($qry) or die(mysql_error());

/* Before we display results let's create our alphabetical navigation. The easiest way to create the navigation is to use character codes and run them through the "for" loop. */
echo "<p>" ;
for ($i = 65; $i < 91; $i++) {
    printf('<a href = "%s?letter=%s">%s</a> | ',
    $PHP_SELF, chr($i), chr($i));
}
echo "</p>" ;

/* now we are ready to display the results. Since out tbl_customers table has only three fileds we will display the results in a paragraphs. In the real world you may need to display the results in a table.
To display the results we will use "do while" loop to fetch the results. If no customers are found we will display an error message. */
if(mysql_num_rows($execute)>0){
do{
echo "<p>" .$result['id']. " " .$result['firstname']. " " .$result['lastname']. "</p>" ;
}while($result = mysql_fetch_assoc($execute));
}else{
echo "<p>No customer found.</p>" ;
}

?>

As you can see it is very easy to create alphabetical sort using PHP/MySQL.