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>';
?>

Usually when you are paginating you have counted how many row there are in total so you know when to do things like this. Does your code do a count of all the possible results rows? — RiggsFolly
– RiggsFolly, Commented Jul 22, 2018 at 1:14
Currently it's 26 but that's not a static number because I uploaded those images and plan to upload more — user7915134
– user7915134, Commented Jul 22, 2018 at 1:15
So you need to start the process by counting all the possible rows — RiggsFolly
– RiggsFolly, Commented Jul 22, 2018 at 1:15
Could I just use a mysqli_num_rows function and return the amount of rows or something — user7915134
– user7915134, Commented Jul 22, 2018 at 1:16

RiggsFolly · Accepted Answer · 2018-07-22 01:23:10Z

0

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>';
}

answered Jul 22, 2018 at 1:23

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user7915134 Over a year ago

Okay at first I attempted using your code and for some reason it wasn't working so I changed it a little bit but went back to yours and now it works so I don't know what that was about but now it's working like I wanted so thank you

Michael - sqlbot · Accepted Answer · 2018-07-22 22:53:21Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related