如何在不使用多个页面的情况下以不同方式对mysql表中的内容进行排序？

Question

I have a section on my website where users can post comments. Just above these comments I have 4 links - Oldest, Newest, Top Rated and Worst Rated. Currently I have 4 different pages for each of these links.

oldest.php - sorts the comments by date and time ascending

newest.php - sorts the comments by date and time descending

top.php - sorts the comments depending on how many likes they have

worst.php - sorts the comments depending on how many dislikes they have

They are all being sorted with a mySQL statement such as

$sql = "SELECT * FROM comments ORDER BY date DESC, time DESC LIMIT $offset, $rowsperpage";

I'm just wondering is there any way to order these comments by using just one page instead of having 4 different pages?

Any help would be greatly appreciated.

Answer 1

Yes, you pass the sort column and direction in the URL.

$type = 'new'; // default order
$cols = array('old', 'new', 'worst'); // array with possible options columns, to prevent SQL injection
if (in_array($_GET['type'], $cols) {
    $type = $_GET['type'];
}
$order = (strtolower($_GET['order']) == 'asc')?'ASC':'DESC'; // again - to prevent bad data
$sql = "SELECT * FROM comments ORDER BY {$type} {$order}, time DESC LIMIT $offset, $rowsperpage";

If you have different queries, just use a switch() statement, and change the query accordingly for each type of order.

// use the same order as before
switch ($_GET['type']):
case 'old':
     $sql = " ... ";
break;

// more options
default:
    // default can be used for the most common option, for example when you first enter the page with no type argument in the URL
break;

One more thing - to generate the URLs you can use this:

$cols = array('old', 'new', 'worst'); // you can make this array a config variable
$order = array('asc', 'desc');
foreach ($cols as $col) {
    foreach ($order as $ord) {
        echo "<a href='index.php?type={$col}&order={$ord}'>".ucwords($col). ' ' . ucwords($ord)"</a>";
    }
}

This will print all the types with all the possible orders. You should play around with this, you can do some neat, dynamic stuff.