PHP & MySQL Pagination

PHP & MySQL Pagination

Here is a nice starting point:

<?php

// insert your mysql connection code here

$perPage = 10;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$startAt = $perPage * ($page - 1);

$query = "SELECT COUNT(*) as total FROM redirect
WHERE user_id = '".$_SESSION['user_id']."'";
$r = mysql_fetch_assoc(mysql_query($query));

$totalPages = ceil($r['total'] / $perPage);

$links = "";
for ($i = 1; $i <= $totalPages; $i++) {
  $links .= ($i != $page ) 
            ? "<a href='index.php?page=$i'>Page $i</a> "
            : "$page ";
}


$r = mysql_query($query);

$query = "SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \' 
ORDER BY 'timestamp' LIMIT $startAt, $perPage";

$r = mysql_query($query);

// display results here the way you want

echo $links; // show links to other pages

Pagination MySQL PHP

You'd be better off thinking about the principles of how it works rather than just looking at the code.

Firstly, you need to know:

• How many records are there in the table? You can do that with COUNT()
• How many entries to show per page? You may fix this, to say 10 per page, or any number.

You then need to understand how LIMIT works. If you do something such as LIMIT 50, 10 it means use 50 as the starting point ("from the 51st record in the table" - remember the indexes start at 0) and get the next 10 rows. The latter number, 10, is the number of rows you want to show per page.

In terms of constructing the links the easiest method is to make the ?page= parameter in the URL the first value for the LIMIT query because this changes per page (e.g. 50 in the example above), where as you know the other number (10) is constant. You can produce these links by doing a ceil on the number of records in the table, divided by the number of records per page. This will output the appropriate numbers for the URL.

Let's say you had 362 records in your database and wanted to show 10 per page. This would produce the URL's:

$per_page = 10;
$records_in_db_table = 362; // You must calculate this from a COUNT() query
$n = ceil($records_in_db_table / $per_page);

for ($x=0; $x<$n; $x++) {
    $page = ($x * $per_page);
    echo '?page=' . $page;
}

The above code outputs:

?page=0
?page=10
?page=20
?page=30
// ...
?page=360

Then you just feed these in to your query's LIMIT condition, e.g.

• ?page=10 == LIMIT 10, 10
• ?page=20 == LIMIT 20, 10
• ?page=30 == LIMIT 30, 10
• etc...

It's also worth noting that you don't need to care about what happens if you're trying to LIMIT to more records than exist. For example the last URL (?page=360) will do LIMIT 360, 10. There are only 362 records in your database so you might assume this won't work as there are only 2 records that could be returned from that query. However, it will just return the last 2, no problem.

Equally if you try and use a number which is way beyond the total number of records (?page=99999 which gives LIMIT 99999, 10) it will just return an empty result set, not an error. Some people code things in to do a simple check, e.g.

if ((int)$_GET['page'] > $records_in_db_table) { 
    // Display error message
}

Remember that you should sanitise $_GET['page'] or at the very least cast it to an integer. Do not inject anything from $_GET directly into your query, and use parameter binding (e.g. PDO) if possible.

You may also want to look at DataTables (https://datatables.net/) since this does a lot of what you need without writing any such code and works well with Bootstrap.

Pagination in one to many relationship with PHP and MySQL

If I understand your needs :

    SELECT u.id, u.userName , m.message
    FROM user u 
    INNER JOIN messages m ON m.userId = u.id
    INNER JOIN (SELECT u.id as user_id FROM user u LIMIT 0, 6) as users
        ON users.user_id = u.id
    WHERE m.date BETWEEN '12-12-2015' AND '12-12-2018';

How to create pagination with PDO PHP

Here is a simple approach to pagination:

<?php
$limit = 2;
$query = "SELECT count(*) FROM kategori";

$s = $db->query($query);
$total_results = $s->fetchColumn();
$total_pages = ceil($total_results/$limit);

if (!isset($_GET['page'])) {
    $page = 1;
} else{
    $page = $_GET['page'];
}



$starting_limit = ($page-1)*$limit;
$show  = "SELECT * FROM kategori ORDER BY id DESC LIMIT ?,?";

$r = $db->prepare($show);
$r->execute([$starting_limit, $limit]);

while($res = $r->fetch(PDO::FETCH_ASSOC)):
?>
<h4><?php echo $res['id'];?></h4>
<p><?php echo $res['nama_kat'];?></p>
<hr>
<?php
endwhile;


for ($page=1; $page <= $total_pages ; $page++):?>

<a href='<?php echo "?page=$page"; ?>' class="links"><?php  echo $page; ?>
 </a>

<?php endfor; ?>

Pagination: Previous and Next using PHP

I've finally figured out what the problem was. Here is what I came up with:

<?php
//browsing feature of database
include ('connection.php');  
include ('header.php');
$count_query = mysql_query("SELECT NULL FROM residents");
$count = mysql_num_rows($count_query);

//pagination starts here
if(isset($_GET['page'])){
//$page = preg_replace("##[^0-9]","",$_GET['page']);
$page = $_GET['page'];
//echo "Page is ".$page;
}else{
$page = 1;
//echo "Page is one";
}
$perPage = 1;
$lastPage = ceil($count/$perPage);
if($page <1){
$page = 1;
}else if($page > $lastPage){
$page = $lastPage;
}

$limit = "LIMIT " .($page-1) * $perPage .", $perPage";

$query = mysql_query("SELECT * FROM residents ORDER BY ID ASC $limit");
if($lastPage != 1){


if($page != $lastPage){
    $next = $page + 1;
    $pagination.='<nav><ul class="pager"><li><a href=Browse.php?page='.$next.'>Next</a>          </li></ul><nav>';
}
 if($page != 1){
    $prev = $page - 1;
    $pagination.='<nav><ul class="pager"><li><a href=Browse.php?    page='.$prev.'>Previous</a></li></ul></nav>';
} 
}


while($row = mysql_fetch_array($query)){
$output.='<p><img src="upload/' . $row['Picture'].'" width="70" height="70"/></p>';
$output.= $row['FirstName']. '<br />';
$output.= $row['LastName']. '<br />';
$output.= $row['Address']. '<br />';
$output.= $row['Birthday']. '<br />';
$output.= $row['FormerResidence']. '<br />';
$output.= $row['Career']. '<br />';
$output.= $row['Education']. '<br />';
$output.= $row['SpecialInterests']. '<br />';


}
?>






<?php
    /*while($person = mysql_fetch_array($result)) {
        echo "<h3>" . $person['FirstName'] . "</h3>";
        echo "<p>" . $person['LastName'] . "</p>"; 
        echo "<p>" . $person['Address'] . "</p>";
        echo "<p>" . $person['Birthday'] . "</p>"; 
        echo "<p>" . $person['FormerResidence'] . "</p>";
        echo "<p>" . $person['Career'] . "</p>";
        echo "<p>" . $person['Education'] . "</p>";
        echo "<p>" . $person['SpecialInterests'] . "</p>";
        echo '<p><img src="upload/' . $person['Picture'].'" width="70" height="70"/>      </p>';
    }*/
?>
<h1>Browse Residents</h1>
<!-- Comment start
        <a href="nextandprevious.php?page='.$previous.">Previous</a>
        <a href="nextandprevious.php?page='.$next.">Next</a>
             Comment end -->

        <?php echo $output;?>
        <?php echo $pagination;?>

PHP - MySQL query with Pagination

First of all find out how many games are there for a specific category

change the line

$query2 = mysql_query("SELECT * FROM Games WHERE category = '$code' ");

to

$sql="SELECT * FROM Games WHERE category = '$code' ";
$query_count=mysql_query($sql);

Add following after it

$per_page =30;//define how many games for a page
$count = mysql_num_rows($query_count);
$pages = ceil($count/$per_page);

if($_GET['page']==""){
$page="1";
}else{
$page=$_GET['page'];
}
$start    = ($page - 1) * $per_page;
$sql     = $sql." LIMIT $start,$per_page";
$query2=mysql_query($sql);

Then display the numbers of pages where you want

<ul id="pagination">
        <?php
        //Show page links
        for ($i = 1; $i <= $pages; $i++)
          {?>
          <li id="<?php echo $i;?>"><a href="linktoyourfile?c=<?php echo $c;?>&page=<?php echo $i;?>"><?php echo $i;?></a></li>
          <?php           
          }
        ?>
      </ul>

Use CSS for pagination this will do the trick

Pagination with PHP and MySQL?

For making limit the amount of data displayed

<?php 
$sqlCount = "select count(id_transaksi) from barang";  
$rsCount = mysql_fetch_array(mysql_query($sqlCount));  
$banyakData = $rsCount[0];  
$page = isset($_GET['page']) ? $_GET['page'] : 1;  
$limit = 2;  
$start_from = $limit * ($page - 1);  
$sql_limit = "select * from barang ORDER BY id_transaksi limit $start_from, $limit";  
$result = mysql_query($sql_limit);  ?>

For making number of pages / pagination

<?php
$numberofPages = ceil($amountData / $limit);  
echo 'Pages: ';  
for($i = 1; $i <= $numberofPages; $i++){  
 if($page != $i){  
 echo '[<a href="showReport.php?page='.$i.'">'.$i.'</a>] ';  
 }else{  
 echo "[$i] ";  
 }  
}  
?>  

Based on my question, paging.php isn't any longer used. Hope can help anyone who gets problem like me before, cheers! ;)

---

Related Topics