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?

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

Answer 2

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.