Making an HTML table sortable

[laarchie]

Hi guys. So far I have find the forum very helpful in getting started with my first project. I have one issue now which I am sure has come up before but in slightly different ways.

 

I have got a PHP script that obtains data from MySQL and outputs it as a table in HTML.

 

I need to make this table sortable, i.e. be able to click the headings and sort in asc/desc order.

 

I have tried loads of Javascript codes I have found but none seem to work for my table.

 

Here is the code for my script:

<div id="tablecontent">


      <?php

      //Obtain minimum id

      $sqlMinID = "SELECT MIN(id) as min_id FROM Players";
      $resultminID = mysql_query($sqlMinID);
      $rows11 = mysql_fetch_array($resultminID);

      $minimumID = $rows11['min_id'];

      //Obtain maximum id

      $sqlMaxID = "SELECT MAX(id) as max_id FROM Players";
      $resultmaxID = mysql_query($sqlMaxID);
      $rows12 = mysql_fetch_array($resultmaxID);

      $maximumID = $rows12['max_id'];

      $playerID = $minimumID;

      ?>

      <table id="mvp" style="width:100%">

        <thead>
        <tr>
          <td><strong>Most Valuable Player</strong></td>
        </tr>

        <tr>
          <th><strong>Name</strong></th>
          <th><strong>Surname</strong></th>
          <th><strong>Team</strong></th>
          <th><strong>Year Group</strong></th>
          <th><strong>MVP Score</strong></th>

        </tr>
      </thead>

        <?php

        while ($playerID <= $maximumID){


          $sqlFirstName = "SELECT `FirstName` FROM `Players` WHERE `id` = '$playerID'";
          $resultName = mysql_query($sqlFirstName);
          $rows2 = mysql_fetch_array($resultName);

          $firstName = $rows2['FirstName'];


          $sqlSurname = "SELECT `Surname` FROM `Players` WHERE `id` = '$playerID'";
          $resultSurname = mysql_query($sqlSurname);
          $rows3 = mysql_fetch_array($resultSurname);

          $surname = $rows3['Surname'];

          $sqlMVP = "SELECT MVP_Score FROM `Statistics` WHERE `id` = '$playerID'";
          $resultMVP = mysql_query($sqlMVP);
          $rows4 = mysql_fetch_array($resultMVP);

//MUST ADD TEAM FUNCTION

          ?>


<tbody>
          <tr>
            <td><?php echo $firstName; ?></td>
            <td><?php echo $surname; ?></td>
            <td>NONE</td>
            <td>NONE</td>
            <td><?php echo $rows4['MVP_Score']; ?></td>
          </tr>
          <?php $playerID = $playerID + 1 ;
          // close while loop
        }
        ?>
</tbody>
      </table>

Can anyone point me in the right direction?

 

Thanks in advance

 

[cyberRobot]

You could try the technique described here:

http://www.cyberscorpion.com/2011-11/sorting-html-data-tables-part-2-dynamically-sort-in-ascending-and-descending-order/

[mac_gyver]

if your goal is to do this using php, your first step will be to correct the way you are querying the database. actually, you should correct the php code in any case as it has several problems.

 

you should have ONE query that gets the data you want in the order that you want it. the current code, getting the min and max id, then running queries inside of loops, is not workable or efficient. there's no guarantee that your id's will be contiguous and running queries inside of loops is the type of thing that gets a hosting account suspended for using too many resources.

 

you would write one query using JOIN's between the related tables. you would build the ORDER BY term in the query dynamically based on $_GET variables that come from the links in the html table headings.

 

your program logic would need to be -

 

1) determine from any sort related $_GET variables, what the ORDER BY term needs to be. use a default sorting order, such as by last name, first name, if no sort column/direction is selected.

 

2) run one JOIN'ed query that gets the data you want in the order that you want it

 

3) output the html for the table, with the column header items being links that tell the code if to sort by that column and which direction to sort. the direction to sort is either a default (asc), if that column is not the active column being used to sort, and it's actually the opposite direction, if that column is the current column being sorted. the sort links should have get parameters similar to ?sortby=column_name&dir=asc

 

for example, on the first visit to your page, the sort links should all default to dir=asc. if you click on the MVP Score sort link, ?sortby=MVP_Score&dir=ASC, the php code would take the $_GET['sortby'] and $_GET['dir'] and produce an ORDER BY MVP_Score ASC term in the query. note: do not put these values directly into a query as that will allow sql injection, upon which no escape function can protect against and which even prepared queries cannot protect against since these values cannot be put into a prepared query using place-holders. your code needs to validate that the column and direction are only and exactly permitted values.

 

in this example, since one of the html table columns has been clicked on, there is a sortby and dir value, when you build the sort links in the html table headers, you would invert the direction value in the link so that clicking it again will produce the opposite sort result.

 

4) then simply loop over the result set from the one query to output the data in the html table.

[laarchie]

You could try the technique described here:

http://www.cyberscorpion.com/2011-11/sorting-html-data-tables-part-2-dynamically-sort-in-ascending-and-descending-order/

Thanks for this.

[laarchie]

if your goal is to do this using php, your first step will be to correct the way you are querying the database. actually, you should correct the php code in any case as it has several problems.

 

you should have ONE query that gets the data you want in the order that you want it. the current code, getting the min and max id, then running queries inside of loops, is not workable or efficient. there's no guarantee that your id's will be contiguous and running queries inside of loops is the type of thing that gets a hosting account suspended for using too many resources.

 

you would write one query using JOIN's between the related tables. you would build the ORDER BY term in the query dynamically based on $_GET variables that come from the links in the html table headings.

 

your program logic would need to be -

 

1) determine from any sort related $_GET variables, what the ORDER BY term needs to be. use a default sorting order, such as by last name, first name, if no sort column/direction is selected.

 

2) run one JOIN'ed query that gets the data you want in the order that you want it

 

3) output the html for the table, with the column header items being links that tell the code if to sort by that column and which direction to sort. the direction to sort is either a default (asc), if that column is not the active column being used to sort, and it's actually the opposite direction, if that column is the current column being sorted. the sort links should have get parameters similar to ?sortby=column_name&dir=asc

 

for example, on the first visit to your page, the sort links should all default to dir=asc. if you click on the MVP Score sort link, ?sortby=MVP_Score&dir=ASC, the php code would take the $_GET['sortby'] and $_GET['dir'] and produce an ORDER BY MVP_Score ASC term in the query. note: do not put these values directly into a query as that will allow sql injection, upon which no escape function can protect against and which even prepared queries cannot protect against since these values cannot be put into a prepared query using place-holders. your code needs to validate that the column and direction are only and exactly permitted values.

 

in this example, since one of the html table columns has been clicked on, there is a sortby and dir value, when you build the sort links in the html table headers, you would invert the direction value in the link so that clicking it again will produce the opposite sort result.

 

4) then simply loop over the result set from the one query to output the data in the html table.

Thanks a lot for this answer. I do appreciate the problems in my algorithm.

 

However, I am getting the output I do need and the IDs do work. It does make sense to use JOIN but I am not sure my knowledge will allow me to do this. Thanks, though, great response and very well explained.

[brotherZ]

You'll have to add a new parameter to your URL to use for sorting. For example: domain.com/mypage?order=asc and domain.com/mypage?order=desc

You can then add arrows with each of the links mentioned above.

 

Next, you'll need to change your php script and check is the parameter  'order' is set. Use the value of this parameter to conduct your SQL query.

For example: $sqlMaxID = "SELECT MAX(id) as max_id FROM Players ORDER BY max_id ".$_POST['order'];

[laarchie]

if your goal is to do this using php, your first step will be to correct the way you are querying the database. actually, you should correct the php code in any case as it has several problems.

 

you should have ONE query that gets the data you want in the order that you want it. the current code, getting the min and max id, then running queries inside of loops, is not workable or efficient. there's no guarantee that your id's will be contiguous and running queries inside of loops is the type of thing that gets a hosting account suspended for using too many resources.

 

you would write one query using JOIN's between the related tables. you would build the ORDER BY term in the query dynamically based on $_GET variables that come from the links in the html table headings.

 

your program logic would need to be -

 

1) determine from any sort related $_GET variables, what the ORDER BY term needs to be. use a default sorting order, such as by last name, first name, if no sort column/direction is selected.

 

2) run one JOIN'ed query that gets the data you want in the order that you want it

 

3) output the html for the table, with the column header items being links that tell the code if to sort by that column and which direction to sort. the direction to sort is either a default (asc), if that column is not the active column being used to sort, and it's actually the opposite direction, if that column is the current column being sorted. the sort links should have get parameters similar to ?sortby=column_name&dir=asc

 

for example, on the first visit to your page, the sort links should all default to dir=asc. if you click on the MVP Score sort link, ?sortby=MVP_Score&dir=ASC, the php code would take the $_GET['sortby'] and $_GET['dir'] and produce an ORDER BY MVP_Score ASC term in the query. note: do not put these values directly into a query as that will allow sql injection, upon which no escape function can protect against and which even prepared queries cannot protect against since these values cannot be put into a prepared query using place-holders. your code needs to validate that the column and direction are only and exactly permitted values.

 

in this example, since one of the html table columns has been clicked on, there is a sortby and dir value, when you build the sort links in the html table headers, you would invert the direction value in the link so that clicking it again will produce the opposite sort result.

 

4) then simply loop over the result set from the one query to output the data in the html table.

 

I have applied myself and read a bit more and came up with this code. Do you think this is better now?

<div id="tablecontent">


      <?php


      $query = "SELECT Players.FirstName, Players.Surname, Players.SchoolYear, Teams.TeamName, Statistics.MVP_Score ".
       "FROM Players, Teams, Statistics ".
        "WHERE Players.id = Statistics.id";

      $result = mysql_query($query) or die(mysql_error());
      ?>

      <table style="width:100%" >
        <tr>
          <td><strong>MVP Rankings</strong></td>
        </tr>

        <tr>
          <td><strong>First Name</strong></td>
          <td><strong>Surname</strong></td>
          <td><strong>Team</strong></td>
          <td><strong>School Year</strong></td>
          <td><strong>MVP Score</strong></td>


        </tr>

      <?php while($row = mysql_fetch_array($result)){
        ?>
        <tr>
          <td><? echo $row['FirstName']; ?></td>
          <td><? echo $row['Surname']; ?></td>
          <td><? echo $row['TeamName']; ?></td>
          <td><? echo $row['SchoolYear']; ?></td>
          <td><? echo $row['MVP_Score']; ?></td>

        </tr>
        <?php
        // close while loop
      }
      ?>
      </table>

      <?php
      // close connection
      mysql_close();
      ?>