PHP/MYSQL Sorting an echo table of mysql data

Question

I've got a piece of coursework to build a php site using mysql.
I need to be able to sort a table of data based on it's columns. The data is retrieved from a mysql DB using PDO. I then just echo a table while looping through each row of data

Any Ideas as to how I can sort the data by columns? Preferably I'd like to be able to sort the data (ascending) by clicking on the table headers. But i'm open to Ideas.

Code Below: PHP:

function displayBooks($data){
    echo "<table id= \"allBooks\">";
    echo "<th>Book ID</th><th>Title</th><th>Description</th><th>In Stock</th><th>Price</th><th>Cateogry</th>";
    #foreach($data as $row)
    {
        echo "<tr>";
        echo "<td>".$row['bookID']."</td>";
        echo "<td>".$row['title']."</td>";
        echo "<td>".$row['description']."</td>";
        echo "<td>".$row['quantity']."</td>";
        echo "<td>£".$row['price']."</td>"; 
        echo "<td>".$row['name']."</td>"; 
        echo "</tr>"; 
    }
    echo "</table>";

....

//Retrieve all books
    $all_books_query = "SELECT b.bookID,b.title,b.description,b.quantity,b.price,c.name FROM Books b
    INNER JOIN Book_Category bc ON bc.bookID = b.bookID
    INNER JOIN Category c ON c.catID = bc.catID
    GROUP BY b.bookID;";
    $rows = $db->query($all_books_query)->fetchAll();

I've got a whole lot more PHP in the rest of the page. Not sure what else I need to include in this to make more sense of the problem, but any ideas will help.

burlyearly · Accepted Answer · 2016-07-10 15:01:51Z

1

Set up a mechanism to send the user's sorting requirements back to the server (query param? post?), and use those requirements to set up an ORDER BY clause in your MySQL query.

answered Jul 10, 2016 at 15:01

burlyearly

3161 silver badge8 bronze badges

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

Comments

Angel Politis · Accepted Answer · 2016-08-04 11:12:37Z

Here's a simple, non_robust implementation

You must make some active links, with params, like this:

function displayBooks($data){
   echo "<table id= \"allBooks\">";
   echo "<th><a href='index.php?col=1&order_by=1'>Book ID ASC</a>|<a href='index.php?col=1&order_by=2'>Book ID DESC</a></th><th><a href='index.php?col=2&order_by=1'>Title ASC</a>|<a href='index.php?col=2&order_by=2'>Title ID DESC</a></th>";
#foreach($data as $row)
{
    echo "<tr>";
    echo "<td>".$row['bookID']."</td>";
    echo "<td>".$row['title']."</td>";
    echo "</tr>"; 
}
echo "</table>";

And the second poart of script

//Retrieve all books

$order_by = 'ASC';
switch ( (int)$_GET['order_by'] ) {
case 1:
  $order_by = 'ASC';
break;
case 2:
  $order_by = 'DESC';
break;
}


$col= "bookID";
switch ( (int)$_GET['col'] ) {
case 1:
  $col = 'bookID';
break;
case 2:
  $col = 'title';
break;
}


$all_books_query = "SELECT b.bookID,b.title,b.description,b.quantity,b.price,c.name FROM Books b
INNER JOIN Book_Category bc ON bc.bookID = b.bookID
INNER JOIN Category c ON c.catID = bc.catID
GROUP BY b.bookID
ORDER BY $col $order_by";

$rows = $db->query($all_books_query)->fetchAll();

Collectives™ on Stack Overflow

PHP/MYSQL Sorting an echo table of mysql data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related