How to paginate output from mysql?

Hello everyone, when creating a site, I ran into a problem how to split the output of information from the database into several pages. Tried the suggested scripts but they didn’t work. I’m looking for help from a pro.


Answer 1, authority 100%

In mysql, you can limit the selection with LIMIT. For example, select 30 records from a table:
‘SELECT field1, field2… FROM tablenameLIMIT 0,30;’
This query will return 30 records. To get the next 30 – you need to write like this ‘LIMIT 31,30’. I think the scheme is clear: you need to set the page and the number of items to be displayed.
Page * the number of output elements is the 1st digit in the LIMIT construct, the number of output elements is the second.


Answer 2

$currentPage = (isset $_GET['p_id'] and !empty($_GET['p_id'])) ? (int) $_GET['p_id'] : 1;
$limit = 30; // 30   
$offset = $limit * ($currentPage - 1);
$queryCountAll = "select count(1) as countAll from tbl";
$resQueryCountAll = mysql_query($queryCountAll); //   -  .
...
$countAll = ...; // -  .(  )
$queryObjsForCurrentPage = "select * from tbl limit {$offset},{$limit}";
$resObjsForCurrentPage = mysql_query($queryObjsForCurrentPage);
$ObjsForCurrentPage = array();
while($row = mysql_fetch_object($resObjsForCurrentPage)){
   $ObjsForCurrentPage[] = $row;
}

Well, you make a leaflet with links. To do this, you have the number of objects in total and the number of objects per page => find out how many total pages and generate links. Something like this:

$pagesCount = ceil($countAll/$limit);
for($i = 1; $i <= $pagesCount; $i++){
  echo '<a href = "index.php?p_id='.$i.'">'.$i.'</a>';
}

Answer 3

As already mentioned above, LIMITis used for this, we will accept $_GET[‘page’] and try to return this or that page by its value. Moreover, we will complicate the example a little and add a count of the number of pages

//   
define("PER_PAGE_LIMIT", 10); //    1 
//       
function getByPage($page) {
     $limit = ($page-1)*PER_PAGE_LIMIT . ', ' . PER_PAGE_LIMIT;
     $dbResult = mysql_query('SELECT * FROM someTable LIMIT ' . $limit);
     $result = array();
     while($row = mysql_fetch_assoc($dbResult)) {
          $result[] = $row;
     }
     return $result;
}
//     
function countPages() {
     $dbResult = mysql_fetch_assoc(mysql_query('SELECT COUNT(*) as total FROM someTable'));
     $pagesTotal = floor($dbResult['total'] / PER_PAGE_LIMIT);
     return $pagesTotal;
}
if(!empty($_GET['page'])) {
    if(is_numeric($_GET['page']) && $_GET['page'] > 0) {
        $page = $_GET['page'];
        $pagesTotal = countPages();
        if($page <= $pagesTotal) {
             $data = getByPage($page);
             //   ,    
             //     -  
             exit(); // ,   ...
        }
    }
    //  -   
    //   -  ,   404,   
    //  
}

Now, if I didn’t mess up anywhere (because I wrote from the bulldozer as soon as I woke up 🙂 ) – something like this…