通过ajax对Mysql表进行排序

Question

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.

Answer 1

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.