PHP & MySQL - Displaying data correctly

Question

My PHP code that pulls in the data is as follows:

$start = (isset($_GET['start']) ? (int)$_GET['start'] : 0);

$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT $start, 4");
    if (!$result) {
        printf("Error: %s\n", mysqli_error($con));
        exit();
    }

my data is displayed with a LIMIT of 4 per page. I created a "Next" and "Previous" link that displays the next or previous four items.

echo "<table width=\"1024\" align=\"center\" >";
echo "<tr height=\"50\"></tr>";

$prev = $start - 4;
echo '<td><a href="?start=' . $prev . '">Previous Items</a></td>';

$next = $start + 4;
echo '<td><a href="?start=' . $next . '">Next Items</a></td>';

echo "</table>";

The problem I'm facing is that the "Next Items" button keeps going past the number of items I have in my database. I need to restrict this and the "Previous Items" so the "Next Items" link doesn't go beyond what my database has, and the "Previous Items" doesn't go to a negative number. I wasn't sure about how to do this. Any ideas?

Fundamentally, it's a great idea to have a "select count" query that matches your regular "select window" query when doing pages... Without the count, how would you know? — , Dec 04 '13 at 19:24
I had answered a very similar question from you yesterday - http://stackoverflow.com/questions/20364349/multiple-pages-using-mysql-limit-offset which, I thought gave pretty good instruction on pagination. Here you don't seem to even be attempting to determine the number of records in your table. Without that, you are not going to be able to reliably create next/previous links. You also didn't bother the filter your input as I showed so you have SQL injection vulnerability. You really should revisit that example and make sure you understand it before opening additional questions. — Mike Brant, Dec 04 '13 at 19:24
PS - If you strictly only want simple next/prev (without smarter links) and you don't want the extra query. The result will return less rows than expected or 0 rows if you're at the boundary. You could even retrieve e.g. twice the rows you actually use to see if the next page will be there. (avoiding 0 should be pretty obvious) — , Dec 04 '13 at 19:33

adam187 · Accepted Answer · 2013-12-04T19:35:20.510

1

Get number of rows with

SELECT COUNT(*) FROM menuitem

And display links only when they should be displayed

$prev = $start - 4;
if ($prev >= 0) {
    echo '<td><a href="?start=' . $prev . '">Previous Items</a></td>';
}

$next = $start + 4;
if ($next < $count) {
    echo '<td><a href="?start=' . $next . '">Next Items</a></td>';
}

edited Dec 04 '13 at 19:35

answered Dec 04 '13 at 19:29

adam187

3,193
21
15

This works, except for the if ($next < $count) ... – ValleyDigital Dec 04 '13 at 20:11
@PixelCheese Did you fetch number of rows in `menuitem` under `$count` variable ? – adam187 Dec 04 '13 at 20:22
Hey @adam187 - I did.. $count = mysqli_query($con, "SELECT COUNT(*) FROM menuitem"); if (!$result) { printf("Error: %s\n", mysqli_error($con)); exit(); } The error is saying that the object of class mysqli_result could not be converted to int. – ValleyDigital Dec 04 '13 at 20:34
@PixelCheese mysqli_query returns object not data. Try `$count = mysqli_query($con, "SELECT COUNT(*) FROM menuitem")->fetch_row()[0];` fetch_row() method shoud get you data array, and then you get first column with [0] – adam187 Dec 04 '13 at 20:44

score 0 · Answer 2 · answered Dec 04 '13 at 19:25

You could run the query without the limit the first time, replacing the SELECT * with SELECT COUNT() and get the number of entries. Then when running the query, if $start + 4 > count, do not display the Next button and if $start < 1 do not display the Prev button.

PHP & MySQL - Displaying data correctly

2 Answers2