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.

you need just debug your code to see result sql.

Kotu
– Kotu

2013-05-23 04:39:07 +00:00
Commented May 23, 2013 at 4:39 — Kotu
– Kotu, Commented May 23, 2013 at 4:39

isJustMe · Accepted Answer · 2013-05-24 02:17:14Z

4

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.

edited May 24, 2013 at 2:17

answered May 23, 2013 at 4:37

isJustMe

5,4902 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Bjorn Over a year ago

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

Bjorn Over a year ago

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

Bjorn Over a year ago

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 Over a year ago

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

Bjorn Over a year ago

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 · Accepted Answer · 2013-05-24 05:27:32Z

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); ?>

BLaZuRE · Accepted Answer · 2013-05-23 04:39:37Z

0

There are multiple issues here:

You're not stating what your exact problem is and what your expected outcome is.
You have $$price in your code
I've never seen a query string appear as a link without the accompanying page (i.e. <a href="page.php?id=value">Text</a>)
You normally want to do more validity and sanity checks if you're accepting values from the client in any way, especially if they will be a part of a database query.
You're showing us two different variables for your query, $sql at the top and $query at the bottom. Make sure you're using the right variables
From what I remember, HTML asks for double quotes, not single quotes, around values.
Debug until you get to the point where you can find what your problem is or the area where your problems is. var_dump(), print_r(), and echo are your friends.

answered May 23, 2013 at 4:39

BLaZuRE

2,4062 gold badges28 silver badges46 bronze badges

4 Comments

Bjorn Over a year ago

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 Over a year ago

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.

BLaZuRE Over a year ago

@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 Over a year ago

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

Collectives™ on Stack Overflow

Sorting html table with a href and php from sql database

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related