go top

PHP / MySQL select data and split on pages

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.

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

  • Ray

    Ray

    July 30, 2017 at 23:07 pm

    Hi,
    Thank you for your reply. Very much appreciated. This is a great script. I probably didn't explain myself properly.
    In the first part of the script I've managed to select records that equal a dropdown list value ex: $sql = "SELECT * FROM ".$datatable." WHERE errortype = '$errortype' ORDER BY id ASC LIMIT $start_from, ".$results_per_page;
    This part works.
    My problem is when I use the SELECT COUNT - I only want to list the number of pages that relate to the column name that equals a value AND NOT ALL RECORDS. I cannot seem to get this to work for me. Can you please help in the sql query to select count only the records that equal a certain column name and display only those pages that relate to that.
    I hope I've explained myself a little better this time.
    I'm stuck and Your help would be greatly appreciated
    Regards
    Ray

  • razmon

    razmon

    June 22, 2017 at 01:47 am

    Hi firstly thank you for prompt reply regarding selecting 20 rows at a time.
    It's a fantastic tutorial
    I've tried taking the bits of code that I thought were relevant but somehow the code doesn't work for me.
    I think I'm getting confused with what code needs to be placed without any comments.
    Would it be possible for someone to combine the relevant code for me. I can change database and table names.
    I've tried and tried and am confusing myself.
    Your help would be greatly appreciated if you could do this for me.
    Many thanks for any help you can give me
    Ray

  • Custom Web Application Development

    Custom Web Application Development

    June 6, 2017 at 10:11 am

    Thanks sir,

    This tutorial was really helpful to set the pagination with limits in php.
    Its really awesome and easily to understand.

    Thanks again.,

    • razmon

      razmon

      June 22, 2017 at 00:58 am

      Hi
      I'm a novice in mysql and php. The tutorial is great but I can't get to work for me.
      Would you be able to put all the code together for me.
      This would be greatly appreciated if you could help me out please
      Many thanks - Ray

  • Vikram

    Vikram

    May 25, 2017 at 19:50 pm

    How can I add a drop down filter to filter records and then show the filtered records pagewise

  • DonnaSkeby

    DonnaSkeby

    May 14, 2017 at 13:20 pm

    Absolutely NEW update of SEO/SMM software "XRumer 16.0 + XEvil":
    captcha solution of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another types of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM software: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other software.

    Interested? There are a lot of introducing videos about XEvil in YouTube.
    See you later!

  • Mike Guarino

    Mike Guarino

    May 5, 2017 at 21:57 pm

    Thank You. Great tutorial !

  • Norbert

    Norbert

    March 22, 2017 at 07:31 am

    Thank you a lot for this wonderful explanation...but hey am doing my php project i want to connect to the database,select only one row from the table randomly and print it on the html page daily(each 24 hours) baased on randomization

  • HIM

    HIM

    March 16, 2017 at 17:08 pm

    Great tutorial, very helpfull

  • moussa

    moussa

    February 8, 2017 at 13:17 pm

    I would like to display in an html form the contents of my table record per record.
    the j th page will display the j th record. How can I do it? In asp.net it is very easy but in php it seems to be difficult.

  • Vinit

    Vinit

    December 28, 2016 at 08:24 am

    The article is great for a learner but i have a question here. we have a customer data with unique ID number and other records like name date and address.
    Now i would like to do that there should two search boxes in first data will be fetch by their ID number (which will be entered by user ) and in the other by date.
    Can you help me here to write the code.
    Thanks!


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