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   lt tr gt      echo   lt td align  center  gt  lt img src        row  picturepath           gt  lt  td gt      echo   lt td align  center  gt      row  name       lt  td gt   lt td align  center  gt   lt input type  button  value  More Info   onclick   window location  more info php      gt   lt  td gt      echo   lt td align  center  gt      row  price       lt  td gt   lt td align  center  gt   lt input type  button  value  Add to Order  onclick    gt   lt  td gt      echo   lt  tr gt       echo   lt  table gt     mysqli close  con          gt         lt table width  1024  align  center   gt           lt tr height  50  gt  lt  tr gt               lt tr gt                   lt td width  80   align  right  gt                       lt a href  itempage2 php  gt NEXT lt  a gt                   lt  td gt                   lt td width  20   align  right  gt                       lt a href    gt MAIN MENU lt  a gt                   lt  td gt               lt  tr gt       lt  table gt    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

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.

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

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

[php] Pagination using MySQL LIMIT, OFFSET

Examples related to php

Examples related to mysql

Examples related to limit

Examples related to offset