TharinduLucky TharinduLucky - 6 months ago 11
MySQL Question

Get all records order by date and time and with limits

In my website there's a page to display all the records in a table. So, I have a simple pagination mechanism.

For the pagination, I'm querying the table with limits according to the page.

$query = "SELECT * ";
$query .= "FROM records ";
$query .= "LIMIT $start, $limit;";


This works perfectly for me.

However, the order I need to get records is not the order of the table. It's should be according to the date and time of the records. (There're columns for date and time).

So, when I changed the query to this, it always gives me same records in every page.

$query = "SELECT * ";
$query .= "FROM records ";
$query .= "WHERE published = '1' ORDER BY date DESC, time DESC ";
$query .= "LIMIT $start, $limit;";


What I need is, all the records should order by date and time and they should also respect to
$start
and
$limit
variables.

It's basically the actual way pagination works. How to implement it with this..

Answer

The standard pattern for this is something like

$itemsPerPage = 10;
$page = (int) $page; // get $page from route or $_GET['page'] or wherever
$pageNum = (!empty($page)) ? (int) $page - 1 : 0;
$start = $pageNum * $itemsPerPage;

$query = "SELECT * ";
$query .= "FROM records ";
$query .= "WHERE published = '1' ORDER BY date DESC, time DESC ";
$query .= "LIMIT $start, $itemsPerPage;";

Remember its start plus number of items, not start to end.