jump to navigation

Query Mysql in Alphabetical Order with PHP January 17, 2012

Posted by Tournas Dimitrios in PHP.
trackback

PHP’s  “best mate” , Mysql  has a rich set of SQL-statements . One of these statements ,  “ORDER BY ” ,  takes  a column name that you specify and sort it in alphabetical order (or numeric order if you are using numbers ) . Then when you use mysqli’s  fetch_assoc  statement to print out the result , the values are already sorted and easy to read . Combining Mysql’s functionality with  dynamically  generated PHP-code we can produce excellent  results .

This article presents a practical example of a dynamically generated alphabet  link (navigation menu)  . Clicking  the links will query Mysql ,  which will return the proper results . To demonstrate this functionality we will query the “world” database , it has recorded the names of  300 countries  . The user can click on the first letter of the alphabet and the table will list all countries with the selected letter . The following prototype simulates the final result , a live demo is on this link >>> .

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z |
Results found –> 9

Name Code
Laos LAO
Latvia LVA
Lebanon LBN
Lesotho LSO
Liberia LBR
Libyan Arab Jamahiriya LBY
Liechtenstein LIE
Lithuania LTU
Luxembourg LUX

The code is as follows :

<?php
include 'DB.test.php'; // Your DB-credentials 
$db = new mysqli("$db_host","$db_user","$db_password","$db_name");
if(mysqli_connect_error()) {
printf("Connection failed:%s",mysqli_connect_error()) ;
exit() ;
	}
/* 
Get the letter user clicked on and assign it a variable  
Always sanitize user's submited input !!!!!!! 
*/
$sort = isset($_GET['firstLetter']) ? filter_input(INPUT_GET, 'firstLetter',FILTER_SANITIZE_URL) : "" ; 
if($sort == "") {
$sql = "SELECT * FROM World_country ORDER BY Name ASC " ;
	}else{
$sql = "SELECT * FROM World_country WHERE Name LIKE '$sort%' ORDER BY Name ASC" ;
	}
$execute = $db->query("$sql");
for ($i = 65; $i < 91; $i++) {
    printf('<a href="%s?firstLetter=%s">%s</a> | ', $_SERVER['PHP_SELF'] , chr($i), chr($i));
	}
	printf('<a href="%s">ALL</a> | ', $_SERVER['PHP_SELF'] );
echo "<br>" ;

$rowcount = $execute->num_rows ;
echo "<b style='color:red'>Results found --> $rowcount</b>" ;

echo "<table id=\"thetable\" cellspacing=\"0\" width=\"520\">";
echo "<tr> <th>Name</th> <th>Code</th> </tr>";


$c = 1;
if ($rowcount > 0 ) {
	$row = $db->query($sql) ; 
    while ($row = $execute->fetch_assoc()) {
       // printf ("%s (%s)\n", $row["Name"], $row["Code"]);
		//echo "<br>";
	$color = ($c++%2==1) ? 'bgcolor=#F7D684':  'bgcolor=#ddd' ;	
	echo "<tr   $color><td WIDTH='350'>"; 
	echo $row['Name'];
	echo "</td><td WIDTH='50'>"; 
	echo $row['Code'] ;
	echo "</td></tr>"; 
		
    }
	}

echo "</table>";



?>

Comments»

1. Socialize and Promote yourself - January 24, 2012

Remarkculous! Webpage, Thanks! Keep up the great work.
meengle.net

2. Deferred Annuities - January 29, 2012

Hi there i am kavin, its my first time to commenting anywhere, when i read this paragraph i thought i could also make comment due to this sensible piece of writing.

tournasdimitrios1 - January 29, 2012

@Kevin
Welcome and thanks for stopping on this blog .

3. Andrew - May 25, 2013

Great job! I will be using this in our CRM if you don’t mind! Running

4. Shahbaz Ahmed Bhatti - January 2, 2014

Love this Tutorial. Manay Thanks Brother

tournasdimitrios1 - January 2, 2014

You are welcome

5. shah - April 28, 2014

great job..Its help me a lot..

6. Bituku Praveen - May 10, 2015

thanks

7. superfifee - May 24, 2015

Thanks so much for this wonderful code! I could kiss 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