PHP & MySQL - Displaying data correctly [duplicate]

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? — user645280
– user645280, Commented Dec 4, 2013 at 19:24
I had answered a very similar question from you yesterday - stackoverflow.com/questions/20364349/… 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
– Mike Brant, Commented Dec 4, 2013 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) — user645280
– user645280, Commented Dec 4, 2013 at 19:33

adam187 · Accepted Answer · 2013-12-04 19:35:20Z

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 4, 2013 at 19:35

answered Dec 4, 2013 at 19:29

adam187

3,4031 gold badge23 silver badges15 bronze badges

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

4 Comments

ValleyDigital Over a year ago

This works, except for the if ($next < $count) ...

adam187 Over a year ago

@PixelCheese Did you fetch number of rows in menuitem under $count variable ?

ValleyDigital Over a year ago

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.

adam187 Over a year ago

@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]

JDubTech · Accepted Answer · 2013-12-04 19:25:10Z

0

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.

answered Dec 4, 2013 at 19:25

JDubTech

1661 silver badge4 bronze badges

Collectives™ on Stack Overflow

PHP & MySQL - Displaying data correctly [duplicate]

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related