PHP / MySQL select data and split on pages
posted on 2006-12-31 10:29:11This 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
returns 20 records sorted by name starting from the first record. This next query
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.
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>
<?
while ($row = mysql_fetch_assoc($rs_result)) {
?>
<tr>
<td><? echo $row[“Name”]; ?></td>
<td><? echo $row[“PhoneNumber”]; ?></td>
</tr>
<?
};
?>
</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
$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.
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.
<?
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):
<?
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>
<?
while ($row = mysql_fetch_assoc($rs_result)) {
?>
<tr>
<td><? echo $row[“Name”]; ?></td>
<td><? echo $row[“PhoneNumber”]; ?></td>
</tr>
<?
};
?>
</table>
$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 |
| Farhang Baghban posted on 2008-07-03 13:23:29 |
| I appreciate you ,that is very helpfull code and description. It works, but could you tell me if there are too many pages and records , how navigation bar will be shown numbers , I mean I need to have something like this 1,2,...,45,46 or more than 45 pages.A breif of numbers string.Thank you for your help. |
| zied posted on 2008-05-20 12:57:51 |
| slt, comment je peut faire une connexion entre mysql et php sachant que j'utilise l'hébergeur dreamweaver. aider moi SVP merçi. |
| saravanan posted on 2008-05-20 12:47:49 |
| phpmysql, datadase value is count to display the front page on php. |
| Mizu posted on 2008-05-20 07:41:25 |
| Very helpful and easy to understand code. Thank you very much! |
| srinivas posted on 2008-04-25 13:40:23 |
| can any one tell me how to select records from different pages and to club them to one name |
| pradeep posted on 2008-03-13 09:27:48 |
| echo “<a href=’pagination.php?page=”.$i.”’>”.$i.”</a> ”; I am getting this error in the above line PHP Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' |
| kamala kannan posted on 2008-01-18 14:19:51 |
| thank you very much |
| Rob Shaffer posted on 2008-01-09 00:51:45 |
| $query3 = "SELECT COUNT (*) from cust_summary WHERE ytd_sales > ".$sales_amt; $result3 = mysql_query( $query3 ) or die(mysql_error()); $row = mysql_fetch_row($result3); echo $row[0]; Returns Error with invalid result??? Any ideas greatly appreciated -------------------- Veselin: try using $query3 = "SELECT COUNT (*) from cust_summary WHERE ytd_sales > '".$sales_amt."'"; $result3 = mysql_query( $query3 ) or die(mysql_error()); and let me know what the exact error message is -------------------- Rob Shaffer: The Quotes fixed the issue.... |
| Hiro Garabedian posted on 2007-09-30 01:04:30 |
| This site is really nice and friendly , offering a very good startups and hints and just for that I think a small contribution of purchasing offered products is the least we can do to keep it going on. Thank you I really appreciate it |
| priyanka posted on 2007-09-19 11:14:44 |
| according to ur code clicking on page 2 ,the value is not pass into page everytime the value of page variable is 1 |