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
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

Curveo posted on September 18, 2009, 3:44 pm
Guys this is cool BUT what if you have say like 200 or more results? Well in my case I have 300 results and i am trying to split them into 5 per page (for a blog software) and the limit key word of MySQL is giving me unexpected results. I think mysql maybe not be sorting my record set correctly. Here is what it says in the MySQL manual: <br>"If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so. "

Any idea how to get around this?

sudhakar posted on September 15, 2009, 4:06 am
I was searching for an example of pagination and this was perfect. Thanks!

Jogesh sharma posted on August 21, 2009, 10:12 am
hi, thanks to share your knowledge it help me a lot:)
but in the end i need to change something cause it just show one page and i had lot of data : )

$sql2 = "SELECT * FROM upload_img";
$rs_result = mysql_query($sql2);
while($row = mysql_fetch_row($rs_result)){
$total_records = $row[0];
//echo $total_records;
$total_pages = ceil($total_records / 5);
//echo $total_pages;
}
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='dataLimit.php?page=".$i."'>".$i."</a> ";
}

Gabriele Favrin posted on July 25, 2009, 1:23 am
Thanks. I was searching for an example of pagination and this was perfect. I translated it to PDO and I'm currently using it with SQLite on PHP5. Thanks!

Ole posted on July 5, 2009, 1:51 am
Hi all, I know this post is old but:

Quote:
"Next we need to find out the total amount of records in our table and the number of pages that we will need. "

Here is another example how u can do that:

<?php... blah blah:

// The query
$query = "SELECT * FROM students ORDER BY name";

// doing the query / result
$result = mysql_query($query);

// Checking the numbers of rows.
$numrows = mysql_num_rows($result);

// If the number of rows is higher than 50, then..
if ($numrows > 50) {
echo "Omg, more than 50 rows!!";
}

?>

:)

Another example, if u wanna make a login page. I'm using "accounts" and username and password

<?php

// Taking and using the variables at config.php (mine's at the bottom of the page)
require_once('config.php');

// Let's say that the username is michael and pw is mich123 (u can use $_POST[''] or whatever you want.
$username = "michael";
$password = "mich123";

// checking the database and tables, looking for a record where username is .. and password is ..
$query = "SELECT * FROM accounts WHERE username='$username' AND password='$password'";

// doing the query..
$result = mysql_query($query);

// Checking if the record exists WITH username and password (a record that has both that username and password)
$numrows = mysql_num_rows($result);

if ($numrows > 0) {
echo "Username and Password exists";
}
else
{
echo "Wrong username and/or password!";
}

?>

I'm using config.php (uses all the variables)

my config.php:

<?php

$host = "";
$id = "";
$pw = "";
$db = "";

$con = mysql_connect($host, $id, $pw);

$database = mysql_select_db($db);

?>

Have Fun! :D

my msn: respiant@gmail.com
or email me at the same email address.

my website: www.oledev.com making some php and mysql projects, we are looking for people to join us!! ;)

Bindiya posted on June 11, 2009, 11:38 am
Hi, thanks for this scripy. I have tried this and it works. But, I am new to coding and am bit stuck with a simple issue, I think. I have created a pagination.php file with the above code which gives the results in the browser when I click the url: http://myhost/pagination.php

But I am not sure how to integrate this code into my website. I currently have a static products.html page (and few other static pages created using kompozer).
I wish to now call the pagination.php on the products page and display products from my mysql db page by page. How do I do that? How do I give value to the 'page' parameter in this case? (there is no query string in my url)
Please let me know the best way to do this. Any help from anyone will be appreciated. Thanks!

Pages:   1   2   3   4   5   
   
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