I have some code that LIMITs data to display only 4 items per page. The column I'm using has about 20-30 items, so I need to make those spread out across the pages.

On the first page, I have:

    $result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4");
{
  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);

    ?> 

    <table width="1024" align="center" >
        <tr height="50"></tr>
            <tr>
                <td width="80%" align="right">
                    <a href="itempage2.php">NEXT</a>
                </td>
                <td width="20%" align="right">
                    <a href="">MAIN MENU</a>
                </td>
            </tr>
    </table>

You'll notice towards the bottom of the page my anchor tag within lists the second page, "itempage2.php". In item page 2, I have the same code, except my select statement lists the offset of 4.

$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4 offset 4");

This works, this way when there is a pre-determined number of items within my database. But it's not that good. I need to create a new page only if there are more items, not hard-coded into it like it is now.

How can I create multiple pages without having to hard-code each new page, and offset?

Answer
    • Using $_GET at the top. If no $_GET, offset is 0. Limit could be hardcoded as a pagination system (for ex. 10 items per page)

First off, don't have a separate server script for each page, that is just madness. Most applications implement pagination via use of a pagination parameter in the URL. Something like:

http://yoursite.com/itempage.php?page=2

You can access the requested page number via $_GET['page'].

This makes your SQL formulation really easy:

// determine page number from $_GET
$page = 1;
if(!empty($_GET['page'])) {
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
    if(false === $page) {
        $page = 1;
    }
}

// set the number of items to display per page
$items_per_page = 4;

// build query
$offset = ($page - 1) * $items_per_page;
$sql = "SELECT * FROM menuitem LIMIT " . $offset . "," . $items_per_page;

So for example if input here was page=2, with 4 rows per page, your query would be"

SELECT * FROM menuitem LIMIT 4,4

So that is the basic problem of pagination. Now, you have the added requirement that you want to understand the total number of pages (so that you can determine if "NEXT PAGE" should be shown or if you wanted to allow direct access to page X via a link).

In order to do this, you must understand the number of rows in the table.

You can simply do this with a DB call before trying to return your actual limited record set (I say BEFORE since you obviously want to validate that the requested page exists).

This is actually quite simple:

$sql = "SELECT your_primary_key_field FROM menuitem";
$result = mysqli_query($con, $sql);
if(false === $result) {
   throw new Exception('Query failed with: ' . mysqli_error());
} else {
   $row_count = mysqli_num_rows($result);
   // free the result set as you don't need it anymore
   mysqli_free_result($result);
}

$page_count = 0;
if (0 === $row_count) {  
    // maybe show some error since there is nothing in your table
} else {
   // determine page_count
   $page_count = (int)ceil($row_count / $items_per_page);
   // double check that request page is in range
   if($page > $page_count) {
        // error to user, maybe set page to 1
        $page = 1;
   }
}

// make your LIMIT query here as shown above


// later when outputting page, you can simply work with $page and $page_count to output links
// for example
for ($i = 1; $i <= $page_count; $i++) {
   if ($i === $page) { // this is current page
       echo 'Page ' . $i . '<br>';
   } else { // show link to other page   
       echo '<a href="/menuitem.php?page=' . $i . '">Page ' . $i . '</a><br>';
   }
}
  • 95
Reply Report
      • 2
    • Wow. I was way off! lol... I'll have a look over everything you wrote and give it a shot. Thank you for your efforts!
    • Do you run into trouble if you reuse the $sql variable ? or does "$row_count = mysqli_num_rows($result); // free the result set as you don't need it anymore mysqli_free_result($result);" get rid of it?
      • 1
    • @ClaaziX $sql is just a variable containing a string. You can reuse the variable name (or not) as you see fit. It has not relation to the query of the result set after the query is run. For example, you could change the value on the line after mysqli_query without any impact on working with the result set.
      • 2
    • @NiCkNewman This is because I am assuming you have page numbers that start with 1 (i.e. 1, 2, 3 ...). When determining the offset to use for your query, you need to have an offset of 0 to get the first page. I subtract the 1 from the page number to get to a zero-based offset. Let's say you have 10 items per page and you want the second page. This offset would be calculated like this for page 2 -> $offset = (2 - 1) * 10 That leave you with a value of 10, which is you desired offset number for your query.

Use .. LIMIT :pageSize OFFSET :pageStart

Where :pageStart is bound to the_page_index (i.e. 0 for the first page) * number_of_items_per_pages (e.g. 4) and :pageSize is bound to number_of_items_per_pages.

To detect for "has more pages", either use SQL_CALC_FOUND_ROWS or use .. LIMIT :pageSize OFFSET :pageStart + 1 and detect a missing last (pageSize+1) record. Needless to say, for pages with an index > 0, there exists a previous page.

If the page index value is embedded in the URL (e.g. in "prev page" and "next page" links) then it can be obtained via the appropriate $_GET item.

  • 3
Reply Report

If you want to keep it simple go ahead and try this out.

$page_number = mysqli_escape_string($con, $_GET['page']);
$count_per_page = 20;
$next_offset = $page_number * $count_per_page;
$cat =mysqli_query($con, "SELECT * FROM categories LIMIT $count_per_page OFFSET $next_offset");
while ($row = mysqli_fetch_array($cat))
        $count = $row[0];

The rest is up to you. If you have result comming from two tables i suggest you try a different approach.

  • 3
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions