通过ajax对Mysql表进行排序
My web app screen looks like this. Upon selection of dropdown and clicking the display button it generates table(no sorting) as displayed below.
My requirement in this table is to sort the table on clicked column header by ascending and descending order i.e. when i click on the name column firstly it should display in ascending and secondly on clicking this button it should display in descending order.
So far I have achieved ascending only .
Firstly I generated unsorted table on click of display button by this script
echo "<tr><th align=\"center\">Name</th><th align=\"center\">Type</th><th align=\"center\">Local Body</th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Name.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Absenteeism</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"District.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Creativity</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Income.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Problem Solving</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Total.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Total</button></th>
</tr>";
while($row = mysql_fetch_array($result)){
$name= $row['Name'];
$dist= $row['District'];
$inc=$row['Income'];
$total=$row['Total'];
echo "<tr><form name=\"form\" id=\"form\"> <td style=\"width: 200px\">".$name."</td> <td style=\"width: 100px\">".$dist."</td> <td style=\"width: 150px\">".$inc."</td>
<td style=\"width: 100px\">".$total." </td>
</form>
</tr>";
}
echo "</table><br /><br /></div>";
When I click on Name column it passes "Name.ASC" id to javascript function .
My Javascript function is
function sortBy(btn){
var a=btn.split(".");
var b="";
(a[1]== "ASC") ? b= "DESC" : b= "ASC";
var c=a[0]+"."+b;
document.getElementById(btn).id= c;
var dataString = 'sorter='+ btn + '&sel_year=' + sel_year + '&sel_trimester=' + sel_trimester;
$.ajax({
type: "POST",
url: "tbl_sort.php",
data: dataString,
cache: true,
success: function(html){
$("#result_table").html(html);
}
});
}
My tbl_sort php page is
if (isset($_POST['sorter'])){
$array = explode(".", $_POST['sorter']);
$sql = "SELECT Name,District,Land,Income,Total from tbl_details where Year='$year' and trimester='$trimester' ORDER BY $array[0] $array[1] ";
$result = mysql_query($sql)or die(mysql_error());
echo "<tr><th align=\"center\">Name</th><th align=\"center\">Type</th><th align=\"center\">Local Body</th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Name.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Absenteeism</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"District.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Creativity</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Income.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Problem Solving</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Total.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Total</button></th>
</tr>";
while($row = mysql_fetch_array($result)){
$name= $row['Name'];
$dist= $row['District'];
$inc=$row['Income'];
$total=$row['Total'];
echo "<tr><form name=\"form\" id=\"form\"> <td style=\"width: 200px\">".$name."</td> <td style=\"width: 100px\">".$dist."</td> <td style=\"width: 150px\">".$inc."</td>
<td style=\"width: 100px\">".$total." </td>
</form>
</tr>";
}
echo "</table><br /><br /></div>";
}
As I have passed initially "column_name.ASC" id so it will do sorting in ascending order only. I am unable to do it in descending order.
I have taken this youtube video as myreference.
Any helps are appreciated.
OK, this isn't a very good way of doing what you're doing, but it'll work (assuming my in-the-head code is ok)
change:
echo "<tr><th align=\"center\">Name</th><th align=\"center\">Type</th><th align=\"center\">Local Body</th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Name.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Absenteeism</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"District.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Creativity</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Income.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Problem Solving</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Total.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Total</button></th>
to:
$idName=($array[0]=='Name')?($array[1]=='ASC')?'Name.DESC':'Name.ASC':'Name.ASC';
$idDistrict=($array[0]=='District')?($array[1]=='ASC')?'District.DESC':'District.ASC':'District.ASC';
$idIncome=($array[0]=='Income')?($array[1]=='ASC')?'Income.DESC':'Income.ASC':'Income.ASC';
$idTotal=($array[0]=='Total')?($array[1]=='ASC')?'Total.DESC':'Total.ASC':'Total.ASC';
$idName=($idName=='')?'Name.ASC':$idName;
$idDistrict=($idDistrict=='')?'District.ASC':$idDistrict;
$idIncome=($idIncome=='')?'Income.ASC':$idIncome;
$idTotal=($idTotal=='')?'Total.ASC':$idTotal;
echo "<tr><th align=\"center\">Name</th><th align=\"center\">Type</th><th align=\"center\">Local Body</th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"".$idName."\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Absenteeism</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"".$idDistrict."\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Creativity</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"".$idIncome."\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Problem Solving</button></th>
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"".$idTotal."\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Total</button></th>
This will toggle the IDs which will allow ASC DESC clicks.
What I would do is change the headings from:
<th align=\"center\"><button type=\"btnEdit\" name=\"edit\" id=\"Name.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Absenteeism</button></th>
to:
<th align=\"center\">Absenteeism<button type=\"btnEdit\" name=\"edit\" id=\"Name.ASC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Asc</button><button type=\"btnEdit\" name=\"edit\" id=\"Name.DESC\" class=\"btn btn-link \" onclick=\"sortBy(this.id)\">Desc</button></th>
That will give you a heading name with two buttons, one for ASCending and one for DESCending.
why bother when there is an eazy to use plugin, go with datatables
$(function(){
$('#example').DataTable();
});
if you need to sort same data, doing it at client side is more preferable than sorting with ajax call. Datatables will handle it nicely for you. Its installation is pretty basic just follow the instructions in the following page