PHP A to Z sorting script

Here is a quick way to filter records from a MySQL table using dynamically generated alphabet links. The MySQL table may look something like this:

id firstname lastname
1 John Smith
2 Joanne Johnson

And here is what the dynamically generated alphabet navigation would look like:

A B C D ….

The script will do following:

  1. get the letter user clicked on
  2. execute MySQL query using that letter
  3. display filtered results (if no letter was clicked we will display all records)


/* 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 " ;
/* 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 "

" ; for ($i = 65; $i < 91; $i++) { printf('%s | ', $PHP_SELF, chr($i), chr($i)); } echo "

" ; /* 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 "

" .$result['id']. " " .$result['firstname']. " " .$result['lastname']. "

" ; }while($result = mysql_fetch_assoc($execute)); }else{ echo "

No customer found.

" ; }


Related Posts: