|
| |
Query to change the display order of records on a field dynamically |
We can reverse the display order or change field on which the order of display is applied by managing the order by sql query. Even we can give this option to visitors to change the order of display as per their requirement. We will learn how to do this here. We will be using PHP in managing or changing the query as per the user clicks on the column header hyper link of the field.
Now let us take our student table we discussed in our basic sql order by tutorial. For our case we will take the records of class four only. We can write our query to display the records in the order of marks obtained by the students from highest to lowest. But here we will give an option for the visitor to change the order from lowest to highest or vice versa if required.
Here is the php code
if(isset($by) and $by=="asc"){
$by="desc";
}else{$by="asc";}
$rt=mysql_query("SELECT * FROM `student` where class='Four' ORDER BY mark $by ");
echo "<table border='0' width='300' cellspacing='0' cellpadding='0' align=center>";
echo "<tr><td><b>Name</b></td>
<td><b><a href='sql_order-change.php?by=$by'>mark</a></b></td></tr>";
while ( $nt=mysql_fetch_array($rt)){
echo "<tr><td>$nt[name]</td><td>$nt[mark]</td></tr>";
}
echo "</table>";
Note that the value of $by we are setting before by an if logical condition and when ever user clicks the header link of the mark table the value of $by changes and the order by query also changes. The value of $by gets either “asc” or “desc”.
Here is the output of this query, click the field header of mark column and see how the order changes
| Name | mark | | Alex John | 55 | | Big John | 55 | | Krish Star | 60 | | John Mike | 60 | | Babby John | 69 | | John Deo | 75 | | Tade Row | 88 | | Gimmy | 88 | | Marry Toeey | 88 |
Read how order by is used in php paging script.
| |
|
|
|
