Pagination using MySQL LIMIT OFFSET

Question

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?

User · Answer

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

User · Answer

A dozen pages is not a big deal when using OFFSET. But when you have hundreds of pages, you will find that OFFSET is bad for performance. This is because all the skipped rows need to be read each time.

It is better to remember where you left off.

User · Answer

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  gt   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  lt    page count   i         if   i      page       this is current page        echo  Page      i     lt br gt         else      show link to other page           echo   lt a href   menuitem php page      i      gt Page      i     lt  a gt  lt br gt

User · Answer

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.

[php] Pagination using MySQL LIMIT, OFFSET

The answer is

Examples related to php

Examples related to mysql

Examples related to limit

Examples related to offset

Tags