Dynamically sort header Table

Question

PriteshP23 0 Posting Whiz in Training

12 Years Ago

How to sort by column header dynamically in the table ?

Table should be sorted by all column.

<?php
    $order = (isset($_POST['sortCostCode']) && strcasecmp($_POST['sortCostCode'], 'desc') == 0) ? 'DESC' : 'ASC';
    $query = 'SELECT * FROM `Humeur_log` ORDER BY `Humeur_log`.`logid` DESC LIMIT 0 , 30' . $order;
    $result = mysql_query($query);
?>
      <tr>
      <th><a href='?sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>logid</a></th>
      <th><a href='?sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Prenom</a></th>
      <th><a href='?sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Nom</a></th>
      <th><a href='?sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Date</a></th>
      <th><a href='?sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Humeur</a></th>
      </tr>
<?php
  while($rows=mysql_fetch_assoc($result))
  {
      echo "<tr>";
      echo "<td>" . $rows['logid'] . "</td>";
      echo "<td>" . $rows['prenom'] . "</td>";
      echo "<td>" . $rows['nom'] . "</td>";
      echo "<td>" . $rows['datelog'] . "</td>";
      echo "<td>" . $rows['Humeur'] . "</td>";
      echo "</tr>";
  }
?>

php

Edited 12 Years Ago by pritaeas because: Not a code snippet.

2 Contributors
10 Replies
2K Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by PriteshP23

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

You will need to pass the table column too, so you can use it in the order by (just like ASC/DESC).

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

<th><a href='?sortcolumn=logid&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>logid</a></th>

If you want a jQuery solution, I can recommend datatables.net.

Edited 12 Years Ago by pritaeas

broj1 commented: Thnx for this link, I just needed it :-) +6

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

You shouldn't have changed line 7. Instead add a line like it to retrieve the sortcolumn, and include it in the query. Just like you use $_POST['sortCostCode'] you can use $_POST['sortcolumn'], like this:

$order = (isset($_POST['sortCostCode']) && strcasecmp($_POST['sortCostCode'], 'desc') == 0) ? 'DESC' : 'ASC';
$column = isset($_POST['sortcolumn']) ? $_POST['sortcolumn'] : 'logid';
$query = "SELECT * FROM `Humeur_log` ORDER BY `$column` $order LIMIT 0,30";

Edited 12 Years Ago by pritaeas

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

PriteshP23 0 Posting Whiz in Training · Answer 1 · 2012-10-29T10:01:14+00:00

Please give me one example. Using jQuery would be more effective? Which one will be best solution?

PriteshP23 0 Posting Whiz in Training · Answer 2 · 2012-10-29T11:57:20+00:00

I have tried. But it gives error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given.

Please let me know why each time it gives error with mysql_fetch/row commands

I would like to knoz the reason.

    $logid=$_POST['logid'];
    $prenom=$_POST['prenom'];
    $nom=$_POST['nom'];
    $humeur=$_POST['humeur'];
    $datelog=$_POST['datelog'];

    $order = (isset($_POST['logid&prenom&nom&datelog&sortCostCode']) && strcasecmp($_POST['logid&prenom&nom&datelog&sortCostCode'], 'desc') == 0) ? 'DESC' : 'ASC';

    $query = 'SELECT * FROM `Humeur_log` ORDER BY `Humeur_log`.`logid` DESC LIMIT 0 , 30' . $order;

    $result = mysql_query($query);
    ?>

    <table>
          <tr>
          <th><a href='?sortcolumn=logid&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>logid</a></th>
          <th><a href='?sortcolumn=Prenom&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Prenom</a></th>
          <th><a href='?sortcolumn=Nom&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Nom</a></th>
          <th><a href='?sortcolumn=Date&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Date</a></th>
          <th><a href='?sortcolumn=Humeur&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Humeur</a></th>
          </tr>
    <?php
      while($rows=mysql_fetch_row($result))
      {
          echo "<tr>";
          echo "<td>" . $rows['logid'] . "</td>";
          echo "<td>" . $rows['prenom'] . "</td>";
          echo "<td>" . $rows['nom'] . "</td>";
          echo "<td>" . $rows['datelog'] . "</td>";
          echo "<td>" . $rows['humeur'] . "</td>";
            echo "</tr>";
      }
    ?>
       </table>
echo "<td>" . $rows['humeur'] . "</td>";

PriteshP23 0 Posting Whiz in Training · Answer 3 · 2012-10-30T08:32:30+00:00

I have tried all. It is not working. In addition, i would like to understand this line

liste.php?sortcolumn=logid&sortcostcode=%22%20.%20($order%20==

 <?php    
    $logid=$_POST['logid'];
    $prenom=$_POST['prenom'];
    $nom=$_POST['nom'];
    $humeur=$_POST['humeur'];
    $datelog=$_POST['datelog'];

    $order = (isset($_POST['sortCostCode']) && strcasecmp($_POST['sortCostCode'], 'desc') == 0) ? 'DESC' : 'ASC'; 
    $column = isset($_POST['sortcolumn']) ? $_POST['sortcolumn'] : 'logid'; 
    $query = "SELECT * FROM `Humeur_log` ORDER BY `$column` $order LIMIT 0,30";
    $result = mysql_query($query);
?>
    <table>
          <tr>
          <th><a href='?sortcolumn=logid&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>logid</a></th>
          <th><a href='?sortcolumn=Prenom&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Prenom</a></th>
          <th><a href='?sortcolumn=Nom&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Nom</a></th>
          <th><a href='?sortcolumn=Date&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Date</a></th>
          <th><a href='?sortcolumn=Humeur&sortcostcode=" . ($order == 'DESC' ? 'ASC' : 'DESC' ) . "'>Humeur</a></th>
          </tr>
    <?php
      while($rows=mysql_fetch_assoc($result)) // Its working but still would like to know various possiblites if exists
      {
          echo "<tr>";
          echo "<td>" . $rows['logid'] . "</td>";
          echo "<td>" . $rows['prenom'] . "</td>";
          echo "<td>" . $rows['nom'] . "</td>";
          echo "<td>" . $rows['datelog'] . "</td>";
          echo "<td>" . $rows['humeurlog'] . "</td>";
          echo "</tr>";
      }
    ?>
       </table>

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 4 · 2012-10-30T09:17:35+00:00

liste.php?sortcolumn=logid&sortcostcode=%22%20.%20($order%20==

It opens liste.php with GET parameters sortcolumn and sortcostcode (so change $_POST to $_GET). The reason you get the garbage after, is because the lines 15-19 are not properly quoted. They should look like this:

<th><a href='?sortcolumn=logid&sortCostCode=<?php echo $order == 'DESC' ? 'ASC' : 'DESC'; ?>'>logid</a></th>

Also, see sortCostCode. PHP is case sensitive, so how you write it should exactly match. Currently line 8 and 15 use a different sortcostcode. Make sure they match.

PriteshP23 0 Posting Whiz in Training · Answer 5 · 2012-10-30T10:04:55+00:00

In fact it works only for once ( logid, prenom, nom ). When i tried first time, it works but after that it remain same.

It should be possible to view in ASC and DESC at any time.

But When i click For Date and Humeur
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given

I think there is small mistake in the code but difficult to understand the flow of code.

Thanks a lot in advanced.

<?php
    $order = (isset($_GET['sortCostCode']) && strcasecmp($_GEST['sortCostCode'], 'desc') == 0) ? 'DESC' : 'ASC'; // good
    $column = isset($_GET['sortcolumn']) ? $_GET['sortcolumn'] : 'logid';
    /* $column = isset($_GET['sortcolumn']) ? $_GET['sortcolumn'] : 'Prenom';
    $column = isset($_GET['sortcolumn']) ? $_GET['sortcolumn'] : 'Nom';
    $column = isset($_GET['sortcolumn']) ? $_GET['sortcolumn'] : 'Date';
    $column = isset($_GET['sortcolumn']) ? $_GET['sortcolumn'] : 'Humeur';*/

    $query = "SELECT * FROM `Humeur_log` ORDER BY `$column` $order LIMIT 0,30";
    $result = mysql_query($query);
?>
<table>
          <tr>
          <th><a href='?sortcolumn=logid&sortCostCode=<?php echo $order == 'DESC' ? 'ASC' : 'DESC'; ?>'>logid</a></th>
          <th><a href='?sortcolumn=Prenom&sortCostCode=<?php echo $order == 'DESC' ? 'ASC' : 'DESC'; ?>'>Prenom</a></th>
          <th><a href='?sortcolumn=Nom&sortCostCode=<?php echo $order == 'DESC' ? 'ASC' : 'DESC'; ?>'>Nom</a></th>
          <th><a href='?sortcolumn=Date&sortCostCode=<?php echo $order == 'DESC' ? 'ASC' : 'DESC'; ?>'>Date</a></th>
          <th><a href='?sortcolumn=Humeur&sortCostCode=<?php echo $order == 'DESC' ? 'ASC' : 'DESC'; ?>'>Humeur</a></th>
          </tr>
    <?php
      while($rows=mysql_fetch_assoc($result)) // Please need help here
      {
          echo "<tr>";
          echo "<td>" . $rows['logid'] . "</td>";
          echo "<td>" . $rows['prenom'] . "</td>";
          echo "<td>" . $rows['nom'] . "</td>";
          echo "<td>" . $rows['datelog'] . "</td>";
          echo "<td>" . $rows['humeurlog'] . "</td>";
          echo "</tr>";
      }
    ?>
</table>

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 6 · 2012-10-30T10:07:53+00:00

The query failed. To find out why, do this:

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

Fix the typo on line 2 (GEST).

Oh, line 17 uses Date while line 27 uses datelog. Use the correct one. Same with 18 and 28.

PriteshP23 0 Posting Whiz in Training · Answer 7 · 2012-10-30T10:18:27+00:00

PriteshP23 0 Posting Whiz in Training

12 Years Ago

Awesome.

Thanks.