PHP Limit rows returned by MySQL and do something if last row returned

Question

I didn't know exactly how to word this question but by do something I mean that I would like to hide or not show my "next" button that is shown below. I have a script that pulls all the images from MySQL and prints them to my page by 30 images per page and the next 30 will create a new page that is activated by my back/next buttons. My "back" button has a if statement if $startrow isn't >= 0 than it won't show but I would like the same concept with my next button when the last row in my database is shown and it hides my next button.

I was thinking if you can detect the first empty row or the last row of the database and if so hide the next button. Otherwise it keeps adding 30 to $startrow when nothing is shown on screen.

I found a script helping me with this here but it didn't tell me how to hide the next button.

<?php
$startrow = $_GET['startrow'];

if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
  $startrow = 0;  
} else {
  $startrow = (int)$_GET['startrow'];
}
?>
<?php

$db = mysqli_connect("localhost", "root", "", "media");
$uploaded = mysqli_query($db, "SELECT * FROM images LIMIT $startrow, 30");

while ($row = mysqli_fetch_array($uploaded)) {
    echo "<div class='img_container'>";
    echo "<li><img class='img_box' src='uploads/images/".$row['image_title']."' ></li>";
    echo "</div>";
}

$prev = $startrow - 30;
if ($prev >= 0) {
    echo '<div class="prevRow"><a href="'.$_SERVER['PHP_SELF'].'?page='.$page.'&startrow='.$prev.'">Back</a></div>';
}
    echo '<div class="nextRow"><a href="'.$_SERVER['PHP_SELF'].'?page='.$page.'&startrow='.($startrow+30).'">Next</a></div>';
?>

Answer 1

Potentially a little faster than the answer from @RiggsFolly, you can modify your existing query to count the rows.

SELECT SQL_CALC_FOUND ROWS * FROM images LIMIT $startrow, 30

Then, after the query returns, you run a second query to get the answer:

SELECT FOUND_ROWS()

The FOUND_ROWS() function returns the number of rows the previous query would have returned, without LIMIT (or an offset).

This is probably not as fast as your original query would be in isolation, but should be slightly faster than SELECT COUNT(...) ... followed by your original query. With small data sets, though, any differences will likely be below measurable limits.

See also https://dev.mysql.com/doc/refman/5.7/en/information-functions.html

You can also combine these things into a stored procedure that accepts items per page and page number, and returns all of the records along with metadata items such as the total number of pages.

Answer 2

You could try something like

$num_rows = 30;  // rows on a page

$db = mysqli_connect("localhost", "root", "", "media");

// get total possible rows
$res = mysqli_query($db, "SELECT count(id) FROM images");
$row = $res->fetch_row();
$total_rows = $row[0];
$res->close();


$uploaded = mysqli_query($db, "SELECT * FROM images LIMIT $startrow, $num_rows");
while ($row = mysqli_fetch_array($uploaded)) {  
    . . .
}


$prev = $startrow - $num_rows;
if ($prev >= 0) {
    echo '<div class="prevRow"><a href="'.$_SERVER['PHP_SELF'].'?page='.$page.'&startrow='.$prev.'">Back</a></div>';
}

if ( $startrow+$num_rows < $total_rows  ) {
    echo '<div class="nextRow"><a href="'.$_SERVER['PHP_SELF'].'?page='.$page.'&startrow='.($startrow+30).'">Next</a></div>';
}