Pagination when selecting data from database

I am extracting data from a database and displaying it, 5 items at a
time. If I were displaying the lot in one go I would use something like
the following code, but how do I do it with the pagination? I can pop
out of the foreach loop when $n gets to 5 but I can’t just pop back in
again when the user asks for the next page…

$query = 'SELECT * FROM talks ORDER BY date_added DESC;';
$result = $db->query($query);
$n = 1;
if ( empty($result) ) {
  echo '<p>No talks in this category!</p>', "\n";
} else {
  foreach ( $result as $row ) {
    $title       = $row['title'];
    whatever;
    $n++;
  }

Thanks

Have a look at the LIMIT keyword in your query, that can specify the start and count for the records it returns. So I’d do a count first to calculate how many pages to show, then pass the starting record through on the “next page” link so you know where to start the next query.

You could pass start and count values via the link and leave your query as it is, just use the start value to decide how many results to ignore at the start of the loop. But that’s inefficient, especially if you’re returning a lot of data on the query.

Thanks @droopsnoot. I’ll look at LIMIT as you suggest.

Looks like I’ll need a prepared statement to add a value for OFFSET.

There’s no reason not to use one. I guess if your code isn’t prompting the user to type in a starting record number then there would be no security implication if you were to just append the value into your query, but a prepared statement is easy to add.

I haven’t got as far as page 2 yet :wink: but I’m guessing I will need to pass the offset as a parameter in my URL. As it’s a number it can be easily sanitised without the need of a prepared statement…

It could be in the URL, or you could use a POST which would make it more difficult for the casual user to mess things up.

1 Like

Thanks. Id it possible to send POST data when clicking on an A HREF?

I don’t think so, I don’t recall doing that but I’d probably have “next” and “prev” buttons.

Yes but only using JavaScript to build and submit a form.

Thanks guys

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.