PHP Pagination Using Mysql Database

Hello All,

Today I am going to explain you PHP Pagination, how it works.

First we need to know about mysql “limit” part for this.

Here is an example query “SELECT * FROM TABLE_NAME LIMIT offset, No_of_records”.

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1

The No_of_records specifies maximum number of rows to return.

Let’s start with PHP to make it dynamic --

// setting default values for pagination

// how many records you want to show on current page

$per_page = 15; // I want to show 15 records on that page

// how many records you have in database table.

$total_records =  50; // I am having 50 records. You can get it by mysql_num_rows();

// now we need to count no. of pages required for showing pagination

$no_of_pages = ceil($total_records/$per_page); // ceil rounded up to the next highest integer. We need it because no of pages will not be a floating numbers.

//we need to show a extra page for remain records. For example here we need 3 pages for 45 records now reaming are 5. So we need a 1 more page for next 5 records.

//setting current page no.

$page = (int)($_GET['page']) ? $_GET['page'] : 1; // if we get the page no in URL then we assign it to our page variable, but first time we need to set it by 1.

//creating limit no of records

$offset = ($page-1)*$per_page; // first time it will be 0 (ZERO).

// now limit query is

$sql = "SELECT * FROM table_name $offset, $per_page";

// next you will can do easily ..

?>