Bjorn Bjorn - 1 month ago 4
HTML Question

Sorting html table with a href and php from sql database

I have a html table that contains product data from an sql table that is spit out with php. I'd like to sort the data by clicking the heading of the table column.

I'm outputting my table like so (php)

$product_list = "";
$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC");

$product_list .= "<tr>
<td>$id</td>
<td><strong>$product_name</strong></td>
<td>$$price</td>
<td>$category</td>
<td>$subcategory</td>
<td>$date_added</td>
</tr>";


Html

<table class="product-list">
<tr>
<th><a href='?sort=id'>ID</a></th>
<th><a href='?sort=product_name'>Name</a></th>
<th><a href='?sort=price'>Price</a></th>
<th><a href='?sort=category'>Category</a></th>
<th><a href='?sort=subcategory'>Subcategory</a></th>
<th><a href='?sort=date_added'>Added</a></th>
</tr>
<?php echo stripslashes($product_list); ?>
</table>


I've tried a few ways to do this from examples online but when I click the header nothing happens.

This is something I've tested

$sort = array('id', 'product_name', 'price', 'category', 'subcategory', 'date_added');

$order = '';
if (isset($_GET['sort']) && in_array($_GET['sort'], $sort)) {
$order .= $_GET['sort'];
}

$query = 'SELECT * FROM products ORDER BY '.$order;

// Then Output the table as above


There is a lot happening on the page, I've only included the code that generates and displays the table so it could be something else stopping this but I'd like to make sure I'm going about things the right way before delving into the rest of the code.

Any suggestions how you would go about this would be greatly appreciated.

Answer

As for your PHP you can use something like this:

$product_list = "";    
$order = isset($_GET['sort'])?$_GET['sort']:'date_added';

$query = "SELECT * FROM products ORDER BY $order ";
$sql = mysql_query($query);

while($row = mysql_fetch_array($sql)){

$product_list .= "<tr>
      <td>".$row['id']."</td>
      <td>".$row['product_name']."</td>
      <td>".$row['price']."</td>
      <td>".$row['category']."</td>
      <td>".$row['subcategory']."</td>
      <td>".$row['date_added']."</td>         
        </tr>"; 

}

For your HTML make sure you include your the name of the page receiving the parameters:

<table class="product-list">
  <tr>
  <th><a href='page.php?sort=id'>ID</a></th>
  <th><a href='page.php?sort=product_name'>Name</a></th>
  <th><a href='page.php?sort=price'>Price</a></th>
  <th><a href='page.php?sort=category'>Category</a></th>
  <th><a href='page.php?sort=subcategory'>Subcategory</a></th>
  <th><a href='page.php?sort=date_added'>Added</a></th>
  </tr>
  <?php echo stripslashes($product_list); ?>
</table>

Bonus:

You can do the sorting on the client side using this jquery plugin.

Comments