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.

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

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

  • navitha

    navitha

    October 3, 2015 at 09:25 am

    how to create page

  • navitha

    navitha

    October 3, 2015 at 09:21 am

    i want to display a product details in multiple page how to create like pages.. i cant understand plz help me

  • Shyam

    Shyam

    September 24, 2015 at 15:44 pm

    The below code i have created from the above. Every thing is working fine for me. you can use this.
    <?php
    include 'database.php';
    $db = new DB_Class;
    $conn = $db->conn();
    if (isset($_GET["page"]))
    {
    $page = $_GET["page"];
    }
    else
    {
    $page = 1;
    };
    $start = $page * 15;

    // start and end point
    if($start == 0)
    $start = 1;
    else
    $start =$start+1;

    for($i=0;$i<15;$i++)
    {
    $end_page = $start+$i;
    }
    $start_from = $end_page;

    $sql = "SELECT * FROM
    (
    SELECT
    ROW_NUMBER() OVER( ORDER BY agenda_id ASC ) AS RowNumber,
    program_name, program_hall
    FROM program_details
    ) Q

    WHERE RowNumber BETWEEN $start AND $start_from";
    $rs_result = sqlsrv_query ($conn,$sql);
    ?>
    <table border="01">
    <tr><th>SNO</th><th>Program Name</th><th>Hall</th></tr>
    <?php
    $j = 0;
    while($row = sqlsrv_fetch_array($rs_result,SQLSRV_FETCH_ASSOC))
    {
    $j++;
    ?>
    <tr>
    <td><?php echo $j; ?></td>
    <td><?php echo $row["program_name"]; ?></td>
    <td><?php echo $row["program_hall"]; ?></td>
    </tr>
    <?php
    };
    ?>
    </table>
    <?php
    $query_pag_num = "SELECT COUNT(agenda_id) FROM program_details";
    $result_pag_num = sqlsrv_query($conn,$query_pag_num);
    $row4 = sqlsrv_fetch_array($result_pag_num);
    $count = $row4[0];
    $total_pages = ceil($count / 15);
    for ($i=1; $i<= $total_pages; $i++)
    {
    echo "<a href='index.php?page=".$i."'>".$i."</a> ";
    };
    ?>

    • Craig Nicholson

      Craig Nicholson

      September 25, 2015 at 18:04 pm

      that doesn't work for me i get

      Parse error: syntax error, unexpected ‘<‘ in /homepages/46/d567671028/htdocs/clickandbuilds/WordPress/LuxorEstates/wp-content/plugins/insert-php/insert_php.php(48) : eval()’d code on line 42


      [insert_php]
      $servername = "......";$username = ".....";$password = "......";$dbname = ".......";
      // Create connection
      $conn = new mysqli($servername, $username, $password, $dbname);
      // Check connection
      if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
      }
      $db = new DB_Class;
      $conn = $db->conn();
      if (isset($_GET["page"]))
      {
      $page = $_GET["page"];
      }
      else
      {
      $page = 1;
      };
      $start = $page * 15;

      // start and end point
      if($start == 0)
      $start = 1;
      else
      $start =$start+1;

      for($i=0;$i<15;$i++)
      {
      $end_page = $start+$i;
      }
      $start_from = $end_page;

      $sql = "SELECT * FROM
      (
      SELECT
      ROW_NUMBER() OVER( ORDER BY Property Number ASC ) AS RowNumber, Property Thumbnail URL, Property Title4HTML, Image Tag FROM Properties, Available
      ) Q

      WHERE RowNumber BETWEEN $start AND $start_from";
      $rs_result = sqlsrv_query ($conn,$sql);

      <table border="01">
      <tr><th>Program Name</th><th>Property Thumbnail URL</th><th>Property Title4HTML</th><th>Property Title4HTML</th></tr>

      $j = 0;
      while($row = sqlsrv_fetch_array($rs_result,SQLSRV_FETCH_ASSOC))
      {
      $j++;

      <tr>
      <td>" . $row['Property Thumbnail URL'] . "</td>
      <td>" . $row['Property Title4HTML'] . "</td>
      <td>" . $row['Image Tag'] . "</td>
      </tr>

      };

      </table>

      $query_pag_num = "SELECT COUNT(Property Number) FROM Properties";
      $result_pag_num = sqlsrv_query($conn,$query_pag_num);
      $row4 = sqlsrv_fetch_array($result_pag_num);
      $count = $row4[0];
      $total_pages = ceil($count / 15);
      for ($i=1; $i<= $total_pages; $i++)
      {
      echo "<a href='index.php?page=".$i."'>".$i."</a> ";
      };
      [/insert_php]

  • Craig Nicholson

    Craig Nicholson

    September 22, 2015 at 18:29 pm

    Hi I am totally new to all of this and have had a good crack at this but i can't seem to get the pagination to show on my page.
    as I am new to this i am using Wordpress and have put the code into a page and i get the 15 properties coming back which is ace, but no pagination is showing for pages.

    can you offer any help at all?

    thanks this site is brilliant and maybe out of my league on this one but i would like pagination on my Our Properties page
    this is the link for testing - http://www.luxor-estates.com/test-page-anything/





    such as:
    [insert_php]
    $servername = ".......";$username = ".....";$password = ".......";$dbname = "..........";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
    $start_from = ($page-1) * 15;
    $sql = "SELECT `Property Thumbnail URL`,`Property Title4HTML`,`Image Tag` FROM `Properties`,`Available` WHERE `Property Available Flag` = `Flag` ORDER BY `Property Number` LIMIT $start_from, 15";
    $rs_result = $conn->query($sql);
    echo "<table>
    <tr><td>Property Thumbnail URL</td><td>Property Title4HTML</td><td>Image Tag</td></tr>";
    while($row = $rs_result->fetch_assoc()) {
    echo
    "<tr>
    <td>" . $row['Property Thumbnail URL'] . "</td>
    <td>" . $row['Property Title4HTML'] . "</td>
    <td>" . $row['Image Tag'] . "</td>
    </tr>";
    }
    echo "</table>";
    $sql = "SELECT COUNT(`Property Number`) FROM `Properties`";
    $rs_result = $conn->query($sql);
    $row = $rs_result->fetch_assoc();
    $total_records = $row[0];
    $total_pages = ceil($total_records / 15);

    for ($i=1; $i<=$total_pages; $i++) {
    echo "<a href='#".$i."'>".$i."</a> ";
    }
    [/insert_php]

  • Maruthi

    Maruthi

    September 21, 2015 at 10:28 am

    <?php
    $sql = mysql_query("SELECT * FROM driver");
    $row = mysql_fetch_row($sql);
    $total_records = 1000;
    $total_pages = ceil($total_records / 25);

    for ($i=1; $i<=$total_pages; $i++) {
    echo "<a href='driverlist.php?page=".$i."'>".$i."</a> ";
    };
    ?>

    How to high light only the selected page?

  • modiset

    modiset

    September 8, 2015 at 16:58 pm

    how do i create a job search like this one -->

    https://www.phpjabbers.com/job-portal-website/#sectionDemo

  • nilesh dabhi

    nilesh dabhi

    August 11, 2015 at 14:01 pm

    thanks very nice code this is running code..............

  • hardik

    hardik

    August 6, 2015 at 11:52 am

    i have problem with the next button when i come to my last 5 records the loop is going on and on but cant understand here is the code help me
    <?php
    include ('config.php');
    $rec_limit=5;
    $sql="SELECT id FROM master";
    $result=mysql_query($sql);
    $arr=mysql_fetch_array($result);
    $rec_count=mysql_num_rows($result);

    if(isset($_GET['page']))
    {
    $page=$_GET['page'] + 1;
    $offset=$rec_limit * $page;

    }
    else
    {
    $page=0;
    $offset=0;
    }
    $left_rec=$rec_count - ($page * $rec_limit);
    ?>

    <html>
    <head>
    </head>
    <link href="css/style.css" type="text/css" rel="stylesheet" />
    <body>

    <table align="center" border="1">
    <tr>
    <th>Id</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Gender</th>
    <th>course</th>
    <th>skils</th>
    <th>Images</th>
    <th>Address</th>
    <th colspan="2">Action</th>
    </tr>
    <?php
    $sql="SELECT * FROM master limit $offset,$rec_limit";
    $result=mysql_query($sql);

    while($arr=mysql_fetch_array($result))
    {
    ?>
    <tr>
    <td><?php echo $arr['id']; ?></td>
    <td><?php echo $arr['fname']; ?></td>
    <td><?php echo $arr['lname']; ?></td>
    <td><?php echo $arr['gender']; ?></td>
    <td><?php echo $arr['course'];?></td>
    <td><?php echo $arr['skils']; ?></td>
    <td><img src="image/<?php echo $arr['image'];?>" height="50" width="100" /></td>
    <td><?php echo $arr['address']; ?></td>


    <td><a href="edit.php?id=<?php echo $arr['id'];?>">Edit</a></td>
    <td><a href="delete.php?id=<?php echo $arr[ 'id'];?>">delete</a></td>

    </tr>

    <?php
    }
    ?>
    </table>

    <div class="pagenation" align="center" >
    <div class="wrapper">
    <?php
    if($page > 0)
    {
    $end= $page-2;
    echo "<a href="display.php?page=$end">PREVIOUS </a> |";
    echo "<a href="display.php?page=$page">NEXT</a>";

    }
    else if($page == 0)
    {
    echo "<a href="display.php?page=$page">NEXT</a> |";

    }
    else if($left_rec > $rec_limit)
    {

    $end = $page-2;
    echo "<a href="display.php?page=$end">PRIVIOUS</a> |";
    }

    ?>
    </div>
    </div>
    </body>
    </html>

  • jishan

    jishan

    June 29, 2015 at 08:48 am

    thanks for detail to me

  • Sameer Mohammed Quraishi

    Sameer Mohammed Quraishi

    June 20, 2015 at 15:38 pm

    Dear Team,

    I would like to Thanks for this tutorial, i need one little help to complete my pagination on Search result page,

    This script is working well in index.php, but other page result.php i am getting page number like

    123123123123 like this

    Please help me to solve this issue,


Add your comment

Captcha