go top

MEGA Sale

  • Colection of 65 PHP scripts for $6 each

PHP / MySQL select data and split on pages

Limited offer!

Get a huge collection of PHP scripts in a special bundle!

Mega Sale Price
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. Check our live demo.

We have MySQL table called "students" holding 100 records with the following fields:
ID: autoincrement ID
Name: varchar(250)
PhoneNumber: varchar(250)

Instead of doing a single SELECT query and display all the 100 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.

We will first define MySQL connection variables and connnect to the database

<?php
error_reporting(0);
$servername = "localhost";
$username = "****";
$password = "****";
$dbname = "****";
$datatable = "students"; // MySQL table name
$results_per_page = 20; // number of results per page

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>


Next thing to do is to make a PHP file called index.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) * $results_per_page;
$sql = "SELECT * FROM ".$datatable." ORDER BY ID ASC LIMIT $start_from, ".$results_per_page;
$rs_result = $conn->query($sql);
?>
<table border="1" cellpadding="4">
<tr>
<td bgcolor="#CCCCCC"><strong>ID</strong></td>
<td bgcolor="#CCCCCC"><strong>Name</strong></td><td bgcolor="#CCCCCC"><strong>Phone</strong></td></tr>
<?php
while($row = $rs_result->fetch_assoc()) {
?>
<tr>
<td><? echo $row["ID"]; ?></td>
<td><? echo $row["Name"]; ?></td>
<td><? echo $row["PhoneNumber"]; ?></td>
</tr>
<?php
};
?>
</table>


Now, when you open index.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) * $results_per_page;


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. index.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(ID) AS total FROM ".$datatable; 
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_pages = ceil($row["total"] / $results_per_page);


The $total_records is now equal to the number of records that we have in our database, in our case 100. 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 / $results_per_page);


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 100 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='index.php?page=".$i."'>".$i."</a> ";
};
?>


Above code will print numbers from 1 to 5 and for each number will create different link.
index.php?page=1
index.php?page=2
index.php?page=3
index.php?page=4
index.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) * $results_per_page;
$sql = "SELECT * FROM ".$datatable." ORDER BY ID ASC LIMIT $start_from, ".$results_per_page;
$rs_result = $conn->query($sql);
?>
<table border="1" cellpadding="4">
<tr>
<td bgcolor="#CCCCCC"><strong>ID</strong></td>
<td bgcolor="#CCCCCC"><strong>Name</strong></td><td bgcolor="#CCCCCC"><strong>Phone</strong></td></tr>
<?php
while($row = $rs_result->fetch_assoc()) {
?>
<tr>
<td><? echo $row["ID"]; ?></td>
<td><? echo $row["Name"]; ?></td>
<td><? echo $row["PhoneNumber"]; ?></td>
</tr>
<?php
};
?>
</table>

<br />

<?php
$sql = "SELECT COUNT(ID) AS total FROM ".$datatable;
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_pages = ceil($row["total"] / $results_per_page); // calculate total pages with results

for ($i=1; $i<=$total_pages; $i++) { // print links for all pages
echo "<a href='index.php?page=".$i."'";
if ($i==$page) echo " class='curPage'";
echo ">".$i."</a> ";
};
?>


This index.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.

If you have any questions you can always use the form below to ask for free help.
Do not forget to check the demo here.

Special Offer!

Get all 65 PHP scripts in a bundle for $6 each only!

Mega Sale Offer

209 Comments to "PHP / MySQL select data and split on pages"

  • HASSANE

    HASSANE

    July 17, 2019 at 08:20 am

    NICE PROJECT

  • Zeljko

    Zeljko

    July 17, 2019 at 05:23 am

    Form comes out blank because php code is not properly marked in this section:

     
    <td><? echo $row["ID"]; ?></td>
    <td><? echo $row["Name"]; ?></td>
    <td><? echo $row["PhoneNumber"]; ?></td>

    It should be like this:

    <td><?php echo $row["ID"]; ?></td>
    <td><?php echo $row["Name"]; ?></td>
    <td><?php echo $row["PhoneNumber"]; ?></td>

  • HASSANE

    HASSANE

    January 9, 2019 at 09:44 am

    nice project

  • sandaru

    sandaru

    August 2, 2018 at 13:10 pm

    thank you, it works well!

    • Dennis

      Dennis

      September 5, 2018 at 11:55 am

      How well?
      Can you please elaborate on your comment.
      Mine works but when i click the navigation to next page it brings a blank page. Why?

  • ivfan ED

    ivfan ED

    July 31, 2018 at 09:52 am

    it works, thanks a lot. you're doing great thing. (y)

  • jinal

    jinal

    July 24, 2018 at 10:41 am

    Can you please describe how to create pagination for filtered values?

  • amal

    amal

    May 21, 2018 at 11:30 am

    To see how I can work, click on the id to send me to another page
    Very necessary Reply

  • Souradeep From Learningocean

    Souradeep From Learningocean

    April 6, 2018 at 05:44 am

    Great Tutorial! This PHP pagination tutorial have all the options. But I think it would be better if you add an option to change the total number of results. What do you think about it?

  • pooja

    pooja

    March 22, 2018 at 14:03 pm

    This is not working -:)

  • Jann king

    Jann king

    February 27, 2018 at 03:15 am

    Thank you very much..it works...big help!!!!

Add your comment

Captcha
    • Free Scripts

      Add great new functionalities to your website with our Free Scripts collection.

      Free scripts
    • PHP Scripts

      Check our extensive collection of top-notch PHP Scripts that will enhance your website!

      Commercial PHP scripts