SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

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
Namemark
Alex John55
Big John55
Krish Star60
John Mike60
Babby John69
John Deo75
Tade Row88
Gimmy88
Marry Toeey88

Read how order by is used in php paging script.

Number of User Comments : 1


Google+

Erkki15-03-2014
How do you get rid of double or triple and so on "by=asc" "by=desc" lines on url line in the browser? Whenever you implement it in your own code the url line just generates more variables "by=asc&by=desc" an so on.
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer