Sorting by date in reviews

Question

In many reviews, there's usually a feature to sort by date, helpfulness, etc... Essentially these work by querying a new MySQL line right?

For example:

$sql = "SELECT * FROM TABLE ORDER BY TimeAdded"
$sql = "SELECT * FROM TABLE ORDER BY Helpfulness"

Or would there be a better way to do it?

Secondly, making pages for reviews. Is it as simple as using a loop and a limit in the MySQL query to show 10 results per page?

Edit: For a huge review site, would letting MySQL handle all the sorting be ideal?

Answer 1

You could either let the database manage the ordering as you have there (recommended) or you could load all the results and then sort them using your PHP code. Alternatively, you could put them all into HTML and sort them using JavaScript.

For pagination, you can use the LIMIT and OFFSET clauses (or LIMIT 10,10) to page through a resultset (of course, if the resultset changes, your pagination may also change).

SELECT * FROM posts ORDER BY helpfulness LIMIT 10,10

OR

SELECT * FROM posts ORDER BY helpfulness LIMIT 10 OFFSET 10

For combining pagination and sorting, I'd definitely recommend leaving it up to the database to handle.

Answer 2

You got it right on both counts, Doug.

For a huge review site, you still want MySQL to sort the results, you just want to make use of server-side optimizations (a good my.cnf, server-side caching, etc) and code caching. That's a whole 'nother ball of wax, but the basics are basically the same.