JavaScript Linux PHP All
Up

Search



Paginating Database Data






Recent




How To Use VPS Servers

You will learn how to use VPS servers correctly via the commands without the need of any interface or control panel....


Get Your Own Virtual Server Hosting

Virtual servers are widely used to host websites due to the great features they provide and the cheap costs....


PHP Database Connection

By having a PHP database connection for your program, it will be easy to append and retrieve records and save the statuses of your program....


Detailed Example Of A JavaScript Object Oriented Code

Learn how to use the properties and methods in the object oriented code and how to call the methods from inside and outside the mother object....


Create A JavaScript Object Oriented Code

By having a JavaScript object oriented code, your program will be more clear and well structured....


PHP MySQL Pagination


PHP MySQL Pagination
1469   php

PHP MySQL pagination is used to partition the data from the database to navigate the webpage easier and faster.



It is impossible to view all the records from the database at once on the webpage, they have to be partitioned by using a PHP MySQL paginating which provides an easy and simple way to show the content stored in multiple records in the MySQL database. In this article, we will show you the easiest and shortest way to paginate your data from A to Z :)

We will use the PHP database class which we already explained to establish a connection between the PHP and the MySQL database.


Let's Go!


The Index Page

First, we need to create a folder for this small project and give it the name pagination. Our main page will be the index page with a PHP extension, i.e. index.php where we will create an instance of the database class to establish the connection with the MySQL. The index page will be placed inside the pagination folder and its initial content looks like:


<?php
/* index.php */

// Including the database class
require_once 'class.database.php';

// Initiating instance of the database class
$database_class_instance = new Database();
?>


Fill The MySQL Database With Sample Records

You can use the MySQL console or the phpMyAdmin to access/manage your MySQL database. We need to create a database for the pagination inside the MySQL and give it the name pagination, here is the MySQL query:


CREATE DATABASE pagination;

Pagination database has been created successfully!


Create Pagination MySQL Database

Let's now create the cities table which will contain different cities (each city will be in a single row). Cities table will include two columns sequence & name. It is created as below:


CREATE TABLE pagination.cities(
sequence INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (sequence)
);

The MySQL query in the MySQL console looks like:


Create Pagination MySQL Table

Now, we will insert into the table the cities (records) that will be paginated.


INSERT INTO pagination.cities (name) VALUES
('Amman'),
('London'),
('Dubai'),
('Istanbul'),
('New York'),
('Tokyo'),
('Cairo'),
('Singapore'),
('Sao Paulo'),
('Baghdad'),
('Rome'),
('Abuja'),
('Sydney'),
('Delhi'),
('Rebat'),
('Montevideo'),
('Jakarta'),
('Toronto'),
('Johannesburg'),
('Madrid');

Here is the MySQL query in the console interface:


Insert Records Into Cities Pagination Table

The Pagination Class

We will create a simple class for the pagination process, the class will be saved in a file that has a PHP extension, let's give it the name class.pagination.php & place it inside the project folder which is pagination. Inside the pagination class, we will create a method (function) called retrieve to get the records from the cities table. The function will take two simple arguments which are current_page & limit_per_page. Our class will have the property next_button to show the next button when we have still coming records in the database and to hide it when no more records to show up.


<?php
/* class.pagination.php */

// Class definition
class Pagination{
// The constructor function
public function __construct(){

}

// The retrieve function
public function retrieve($current_page, $limit_per_page){
// To get the initial sequence to start the pagination from
$initial_sequence = $current_page * $limit_per_page - $limit_per_page;

// Query to select cities from the table
$sql = 'SELECT name FROM cities ORDER BY sequence DESC LIMIT '.$initial_sequence.','.($limit_per_page+1);

// To process the query
$qry = mysql_query($sql);

// We shouldn't proceed if no records in the table
if( !mysql_affected_rows() ){ return; }

// Looping through records
while( $result = mysql_fetch_object($qry) ){
// Adding cities to array
$this->array_cities[] = $result->name;
}

// Resetting the next_button
$this->next_button = false;

// In case records exceed the page limit
if( count($this->array_cities) > $limit_per_page ){
// Set next_button to true
$this->next_button = true;
// Pop last record
array_pop($this->array_cities);
}
}
}
?>

As per the code, we increase the limit by 1 when we apply the select query on the cities table to check if we have a next page after the current selection. In case the number of records we get by the select is greater than the page limit, then it means that the 'next navigation button' should be viewed on the page, otherwise, we will hide it. The retrieved cities are appended inside an array array_cities that we will pop from it one element in case there is more data to show up after the current selection because the number of records to be viewed should match the limit per page number.

In our PHP MySQL pagination example, the order of the records extracted is descending which means the most recent entries will be shown on the top of the page. You can change the order of the records extracted to ascending order by replacing the DESC with the ASC statement in the MySQL select query.



Back To The Index Page

We are back to the index page index.php as we need to include the pagination class before we initiate its instance and to use the GET parameter to grab the current page from the URL in the browser address bar. Also, we will show the HTML buttons (next & previous) to nagivate between the records that are extracted from the cities table. Here is the complete code inside the index page:


<?php
/* index.php */

// Including the database class
require_once 'class.database.php';

// Initiating instance of the database class
$database_class_instance = new Database();

// Including the pagination class
require_once 'class.pagination.php';

// Initiating instance of the pagination class
$pagination_class_instance = new Pagination();

//To receive the current page from the URL
if( isset($_GET['current_page']) ){
$current_page = $_GET['current_page'];
}
else{
$current_page = 1;
}

// Calling the retrieve function to get the records
$pagination_class_instance->retrieve($current_page, $limit_per_page = 5);

// In case no records
if( !isset($pagination_class_instance->array_cities) ){
echo 'No Results Found';
return;
}

// To print the list of cities
foreach($pagination_class_instance->array_cities as $city){
echo $city.'<br>';
}

// To get the pure project URL on address bar without the "?" character
$project_url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
$array_url = explode('?',$project_url);
$project_url = $array_url[0];

// To show next & previous buttons
if($current_page > 1){
echo '<a href="'.$project_url.'?current_page='.($current_page-1).'">Go Previous</a> ';
}

if($pagination_class_instance->next_button == true){
echo '<a href="'.$project_url.'?current_page='.($current_page+1).'">Go Next</a>';
}
?>


As you notice in the code above, we stop the execution of our program by using the return statement when there are no results to be extracted from the database. For instance, if you forgot to add records inside the cities table or if you didn't create the table, the return statement will prevent from showing any exception/error on the homepage.


We didn't add any CSS styles because we need to point out the main matter which is the PHP MySQL pagination, you can add your own CSS styles that suits your interface and also extend the code if you want to add extra functions. The output of our code looks like:


Final Interface For The PHP MySQL Pagination Code

Notice that we have no previous button on the first page and also no next button on the last page because there is nothing left in the cities table to show up.



Conclusion

We've provided a very simple and basic PHP MySQL pagination example that will help you to partition the data extracted from your database tables. No CSS styles have been added because we wanted to concentrate on the main pagination topic, you can add later your own CSS styles and also extra functions. The code has been tested and it is running 100%, if you are unable to run the example on your machine, then please contact us, we will be very happy to provide you with the source code (the project folder and its files) and any assistance you might require :)










Home · About · Contact · Terms · Company Website

· · · ·