Sorting html table with a href and php from sql database

Question

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.

Solution 1

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.

Solution 2

For those who would like to see my final result with each individual column sorting by toggling Ascending or Descending order.

I'm no php guru so I'm sure you will find some better ways of doing this but it worked for me.

Thanks to everyone who helped above especially isJustMe.

PHP

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

$ascdesc = isset($_GET['ascdesc'])?$_GET['ascdesc']:'DESC';
switch(strtoupper($ascdesc))
  {
  case 'DESC': $ascdesc = 'ASC'; break;
  case 'ASC': $ascdesc = 'DESC'; break;
  default: $ascdesc = 'DESC'; break;
  }

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

$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0){
while($row = mysql_fetch_array($sql)){
    $id = $row["id"];
    $product_name = $row["product_name"];
    $price = $row["price"];
    $category = $row["category"];
    $subcategory = $row["subcategory"];
    $date_added = strftime("%b %d, %y",strtotime($row["date_added"]));    

$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>";
} else {
  $product_list = "You have no products listed in your store";
}

HTML

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

Comments

[Bjorn]

1. My expected outcome is to "sort the data by clicking the heading of the table column." E.g. Click ID sort in numerical order of the ID column, Click Price sort in numerical order of the Price column, etc etc. 2. $$Price outputs a numerical value from the $price variable and I've added a $ in front. It works, didn't know it wasn't correct syntax. 3. Didn't know the page needed to be stated, this is the first time I've done this kind of thing and assumed it would look for the query on the current page.

[Bjorn]

4. What checks would you suggest? 5. The two different variables are because the code at the bottom is an example I found on the net, i customised to suit my code on my site. I did mention it was sourced from an example. 6. The single quotes worked, I've had issues with mixing single with double when spitting out html data from php. 7. Will be debugging but looks like my problem was not mentioning the page in the query for a start so basically nothing was happening.

[Bjorn]

Thanks, I tried the above but the page refreshes and does nothing. I'd like to get this to work with php before trying the jquery plugin :P

[BLaZuRE]

@Bjorn 1. I'm not here to decipher your mind. Your issue is because of a certain problem. I don't know your exact problem, so I can't give you an exact solution to that problem. 2. if $price is 5, $$price returns $5 which is probably 0 or undefined. It is also bad practice. 3. Now you know. 4. Checks that are outside the scope of your learning at the moment. Just code for fun for now. 5. It's common to make small mistakes and syntax errors, even for experienced devs. 6. What works for you is different from what works for everyone and different from W3C standards sometimes.

[Bjorn]

as I'm sure you understand I'm not a masterful php coder that is why I asked how others would go about achieving this. I didn't necessarily want my code analysed and my problem deciphered, I was more looking for a way to actually achieve my goal. Thanks for your input, I'll watch what I ask next time :P

[Bjorn]

@ isJustMe - I just get errors, working through the syntax at the moment

[Bjorn]

yeah tried adding the perenthesis, it still just does nothing. I've stripped my code down to just the table output from the original sql query and the sort code so I don't think anything is conflicting.

[isJustMe]

@Bjorn ok I updated it once again, can you please try the new code

[Bjorn]

Sorry still not working. I echoed out $order and it is definitely getting the correct variable. I'm thinking the jquery plugin may be getting looked at very soon :D

[Bjorn]

Ok partially found what's going on. The page is sorting the results but is still showing the original table. When I output the table "sorted" underneath the original table it has been sorted but now shows both tables, I think that makes sense.

[Bjorn]

I now realise I was outputting the table twice, I have the table sorting woo hoo, I think I heard the scream of frustration from here isJustMe lol sorry. Thank you very much for your help. Now to change between DESC and ASC on the same link

[isJustMe]

@Bjorn I'm glad I was able to help , if you really find my help useful you can cosider upvoting and marking as accepted :)