CODEWITHSUNDEEP
phpmysqldatabasedynamiclimit
ValleyDigital
ValleyDigital

Reputation: 1470

MySQL: Update column items using the same webpage

I currently have a table in my database that has dozens of items. The PHP page I have now, labeled firstpage.php only displays 4 items from my database at a time, using LIMIT 4. I want to use the same page, firstpage.php, and have a button that says "Next," so when a user clicks that button, the next 4 items from my database are displayed until there are no more items.

Here is the code I have:

<?php

require("database.php");

$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();
    }

echo "<table width='1024' border='0' cellpadding='10' cellspacing='5' align='center'>
<tr>
<th></th>
<th>Menu Items</th>
<th>Description</th>
<th>Price</th>
<th>Add to Order</th>
</tr>";

 while($row = mysqli_fetch_array($result))
 {
  echo "<tr>";
  echo "<td align='center'><img src=\"" . $row['picturepath'] . "\" /></td>";
  echo "<td align='center'>" . $row['name'] . "</td> <td align='center'> <input type='button' value='More Info'; onclick=\"window.location='more_info.php?';\"> </td>";
  echo "<td align='center'>" . $row['price'] . "</td> <td align='center'> <input type='button' value='Add to Order' onclick=''> </td>";
  echo "</tr>";
 }
echo "</table>";

mysqli_close($con);


echo "<table width=\"1024\" align=\"center\" >";
    echo "<tr height=\"50\"></tr>";
            $next = $start + 4;
    echo '<a href="?start="'.$next.'">Next items</a>';

echo "</table>";

?>

So far, the "NEXT" link isn't loading the next 4 items. Don't know what I have wrong.

Upvotes: 0

Views: 95

Answers (1)

Ofir Baruch
Ofir Baruch

Reputation: 10346

There are thousands of tutorials about it but I'm just going to give you a basic idea of how to so you'll see that it's easy and simple.

SQL LIMIT

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

(From: http://dev.mysql.com/doc/refman/5.0/en/select.html)

Logic Part

We need to have an indication from which row to start the counting (let's call it start), the counting by the way would be a constant value (in your case - 4). The "more" link would help us with it.

$start = (isset($_GET['start']) ? (int)$_GET['start'] : 0);
//the query and the loop (remember to use the expanded format of LIMIT: LIMIT $start,4)

and the button or link (usually it's a link) would be the value of start plus 4:

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

I hope you got the basic idea and would be able to continue on your own.

Upvotes: 2

Related Questions