PHP / MySQL select data and split on pages

Shopping cart:	view and checkout
Total scripts:	2
Discount:	$17.00 (30% off price)
Your price:	$39.00

Home

PHP Tutorials

Calendars

Polls

Miscellaneous

STIVA™ products

Image Galleries

PHP / MySQL select data and split on pages

posted on December 31, 2006, 10:29 am

This tutorial is going to show you how to SELECT data from a MySQL database, split it on multiple pages and display it using page numbers.

We have MySQL table called “students” holding 90 records with the following fields:
Name – varchar(250)
PhoneNumber – varchar(250)
Instead of doing a single SELECT query and display all the 90 records on a single page we can have 5 pages each containing maximum 20 records. To do this we will need to use the LIMIT clause for SELECT command so we can limit the query to show only 20 records. The LIMIT clause also allows you to specify which record to start from. For example this query

$sql = “SELECT * FROM students ORDER BY name ASC LIMIT 0, 20”;

returns 20 records sorted by name starting from the first record. This next query

$sql = “SELECT * FROM students ORDER BY name ASC LIMIT 50, 20”;

shows 20 records sorted again by name but this time it will start from the 50th record.
So basically in this clause (LIMIT start, count) ‘start’ specify the starting record and ‘count’ specifies how many records to show.

Next thing to do is to make a PHP file called pagination.php which will show the first 20 records from our table. The code below selects and then prints the data in a table.

<?php 
if (isset($_GET[“page”])) { $page  = $_GET[“page”]; } else { $page=1; }; 
$start_from = ($page-1) * 20; 
$sql = “SELECT * FROM students ORDER BY name ASC LIMIT $start_from, 20”; 
$rs_result = mysql_query ($sql, $connection); 
?> 
<table>
<tr><td>Name</td><td>Phone</td></tr>
<?php 
while ($row = mysql_fetch_assoc($rs_result)) { 
?> 
            <tr>
            <td><? echo $row[“Name”]; ?></td>
            <td><? echo $row[“PhoneNumber”]; ?></td>
            </tr>
<?php 
}; 
?> 
</table>

Now, when you open pagination.php in your web browser you will see table showing the first 20 records from your ‘students’ table.

The first 2 lines of the above code

if (isset($_GET[“page”])) { $page  = $_GET[“page”]; } else { $page=1; }; 
$start_from = ($page-1) * 20;

are used to create a $start_from variable depending on the page that we want to view. Later you will see that we will pass a “page” value using the URL (e.g. pagination.php?page=2) to go to different pages. Next we need to find out the total amount of records in our table and the number of pages that we will need. To do this we run another query using COUNT() function.

$sql = “SELECT COUNT(Name) FROM students”; 
$rs_result = mysql_query($sql,$connection); 
$row = mysql_fetch_row($rs_result); 
$total_records = $row[0];

The $total_records is now equal to the number of records that we have in our database, in our case 90. We have 20 records per page so the total number of pages that will be needed is 5 (4 pages with 20 records and last page will have 10 records).

Calculating the amount of pages needed using PHP can be done using ceil() function.

$total_pages = ceil($total_records / 20);

We divide the total number of records by records per page and then the ceil() function will round up the result. Now we have 2 new variables - $total_records equal to 90 and $total_pages equal to 5.

To print page numbers and associate URLs to each number we will use for() cycle.

<?php 
for ($i=1; $i<=$total_pages; $i++) { 
	echo “<a href=’pagination.php?page=”.$i.”’>”.$i.”</a> ”; 
}; 
?>

Above code will print numbers from 1 to 5 and for each number will create different link.
pagination.php?page=1
pagination.php?page=2
pagination.php?page=3
pagination.php?page=4
pagination.php?page=5
as you can see each link passes different page value which is used in the SELECT query above.

At the end you should have a file like this (remember to add the MySQL connection string):

<?php 
if (isset($_GET[“page”])) { $page  = $_GET[“page”]; } else { $page=1; }; 
$start_from = ($page-1) * 20; 
$sql = “SELECT * FROM students ORDER BY name ASC LIMIT $start_from, 20”; 
$rs_result = mysql_query ($sql,$connection); 
?> 
<table>
<tr><td>Name</td><td>Phone</td></tr>
<?php 
while ($row = mysql_fetch_assoc($rs_result)) { 
?> 
            <tr>
            <td><? echo $row[“Name”]; ?></td>
            <td><? echo $row[“PhoneNumber”]; ?></td>
            </tr>
<?php 
}; 
?> 
</table>
<?php 
$sql = “SELECT COUNT(Name) FROM students”; 
$rs_result = mysql_query($sql,$connection); 
$row = mysql_fetch_row($rs_result); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / 20); 
 
for ($i=1; $i<=$total_pages; $i++) { 
            echo “<a href=’pagination.php?page=”.$i.”’>”.$i.”</a> ”; 
}; 
?>

This pagination.php file will print a table with maximum 20 records per page and at the bottom 5 page numbers each pointing to a page showing different 20 records.

Do not forget that for a small fee I can add pagination to all your PHP files. Let me know if you need help with this and I will give you a quote.

Comments

Ramana posted on June 20, 2010, 10:16 pm

Nice job... Its wonderful..

Just one small addition:

Near the end, where you define links to each page,
the links dont actually tell which page the user is in. So I would recommend a small "IF" statement :-

for ($i=1; $i<=$total_pages; $i++) {
if($page==$i){
echo "&i";
}else{
echo “<a href=’pagination.php?page=”.$i.”’>”.$i.”</a> ”;
}
};

Good luck!

antony.titson posted on May 29, 2010, 8:35 am

thank u very much its super rocks.. good example

Lierfiskern posted on May 19, 2010, 2:58 pm

HI,
I have a issue around this paging example and hope that somebody can help me.

This page only show records with data from "aar1" and "art1". This parameter are chosen from the visitor on the webpage from a dropdown menu.
The issue is that only the first 20 records are shown and when i click the "Next" or "1 2 3 ... 45 46 " link, the next page show nothing. I can see that paging is correctly counting the number of records, but there is possible something wrong regarding the hyperlinks. If I remove the WHERE statement it works. But I need it :-) Here is my script:

<?php
$aar1=$_REQUEST['aar1'];
$art1=$_REQUEST['art1'];

function side(&$id)
{

$query = "SELECT * from tablename WHERE aar like '$aar1' AND art like '$art1' ";
$result = MYSQL_QUERY($query);
$number = MYSQL_NUM_ROWS($result);

$i = 0;
if ($number == 0) :
print "<CENTER>message to be printed</CENTER>";
elseif ($number > 0) :
while ($i < $number):
$id = mysql_result($result,$i,"id");
$i++;
endwhile;
endif;
}

class Paging {
var $x;
var $display = 20; //number of records pr page
var $XX = '';
var $counter, $number, $pages, $i, $query2,$query, $result2, $result, $num_record;

function DoPaging($startrow, $prevrow, $nextrow) {
$aar1=$_REQUEST['aar1'];
$art1=$_REQUEST['art1'];

$global_db = mysql_connect('bla bla','username','password');
mysql_select_db('bla bla', $global_db);

$this->Query = "SELECT * from tablename WHERE aar like '$aar1' AND art like '$art1' ";
$this->result = mysql_query($this->Query);
$this->num_record = mysql_num_rows($this->result);

if (empty($startrow)) {
$startrow=0;
}

$this->Query2 = "SELECT * from tablename WHERE aar like '$aar1' AND art like '$art1' ORDER BY aar DESC, maned DESC,dag DESC,vekt DESC LIMIT $startrow, $this->display";

$this->result2 = mysql_query($this->Query2);
$this->counter = 0;

$this->number = mysql_num_rows($this->result2);
$this->i = 0;

$teller = 0;

if ($this->number == 0) :
print(" <center>No records!</center>");
elseif ($this->number > 0) :
?>

<?
while ($this->i < $this->number):

// columns in your table
$id = mysql_result($this->result2,$this->i,"id");
$aar = mysql_result($this->result2,$this->i,"aar");
$art = mysql_result($this->result2,$this->i,"art");

?>

// Your own code for displaying result //

<?php

$this->i++;
endwhile;
endif;
print "";
print "";
if ($startrow != 0) {
$prevrow = $startrow - $this->display;
print("<a href=\"$PHP_SELF?startrow=$prevrow&id=$id\">Prev</a>\n");
}
$this->pages = intval($this->num_record / $this->display);
if ($this->num_record % $this->display) {
$this->pages++;
}
if ($this->pages > 1) {
for ($i=1; $i <= $this->pages; $i++) {
$nextrow = $this->display * ($i - 1);
if(!$this->number == 0)
{
if($nextrow == $startrow)
{

print("<a href=\"$PHP_SELF?startrow=$nextrow&id=$id\">$i</a>\n ");
}
else
{
print("<a href=\"$PHP_SELF?startrow=$nextrow&id=$id\">$i</a>\n ");
}}}}

if (!(($startrow / $this->display) == $this->pages - 1) && $this->pages != 1)
{
if(!$this->number == 0)
{
$nextrow = $startrow + $this->display;
print("<a href=\"$PHP_SELF?startrow=$nextrow&id=$id\">Next</a>\n");
}
}

if ($this->num_record < 1) {
print("$this->XX");
}
}
}

$p_obj =& new Paging;
$p_obj->DoPaging($_GET['startrow'],$_GET['prevrow'],$_GET['nextrow']);
print("");

?>

Naira posted on May 18, 2010, 1:46 am

GREAT TUTORIAL, exactly what I needed!

Thanks you very much
Naira

ShadowPuppet posted on May 9, 2010, 7:56 am

dude this saved my life thank you so much :)
I am running a gaming forum and was writing a tournament script from scratch... tournament comments were just getting so long XD

Thanks for this :)

Ambika posted on April 15, 2010, 11:46 am

Hi this one very useful post..
i have one doubt..is it possible to add pagination inside (html) tabs. i have 3 tabs to display datas from mysql database. but i find difficult to do pagintion inside it.. wen i click d next 10 records button am getting an empty page..
can u suggest me some ideas?

linesh jose posted on February 17, 2010, 12:20 am

Thanks a lot :)

Dom posted on December 22, 2009, 9:28 pm

You isset line should look like this

if (isset($_GET['page']) && ($_GET['page']/1==$_GET['page']) && $_GET['page']!=0){

So that a person won't get a mysql error if they input 0 or any letters into the page getter

Venkatesh posted on November 25, 2009, 11:12 am

Thanks. Your code is working. But i clicked the page numbers, it showing the same results in all pages. How to show the next results?

ManojKumar posted on September 29, 2009, 2:58 pm

can you send the questions with answers about PHP/MYSQL to my websites.I am doing my project in PHP/MYSQL


If you have any additions, suggestions or modifications for this example please contact us or use the form below.

Your name:
Your email:	(email address will not be posted on the web site)
Comment:
Verification