The Best and The most Simple Pagination / Navigation using PHP and MySQL

To know more about our simple Create, read, update and delete application, follow these short videos and notes:
First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)
SELECT / LIST Records From Database Table: PHP & MySQL
UPDATE / EDIT Records In Database Table: PHP & MySQL
Delete / Remove Records In Database Table: PHP & MySQL

These are the entries in our table apple:
1 Google USA
2 Apple USA
3 Microsoft USA
4 Oracle USA
10 IBM
11 HP
12 Symantec
13 Adobe
14 Cisco
15 McAfee
16 Red Hat
17 Sun Microsystems
18 Intel
19 Salesforce
20 Facebook
21 Technotip
22 MindTree
23 Tate Consultancy Ser
24 Cognizant
25 Citigroup
26 Maestro
27 Visa
28 KingFisher
29 HDFC
30 ICICI
31 SBI
32 SBM
33 Twitter
34 LinkedIn
35 BlueDart
36 VRL
37 Zappos
38 FlipKart
39 Amazon
40 Yahoo!
41 ebay
42 PayPal

We would split these entries into 5 segments each and display 5 records at a time.

In this video tutorial we’re showing the latest entry first. If you want to show first entry first, then take another database field called date and insert the date and time of the insertion operation. Now using the WHERE clause and LIMIT fetch the data from database and display as you wish.

WHERE clause and LIMIT, to fetch depending on date and time
mysql query

mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date ASC LIMIT $start, 5;

mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date DESC LIMIT $start, 5;

In our tutorial, we have taken id as primary key and as AUTO_INCREMENT field.

$rs = mysql_query("SELECT count(*) FROM apple");
 $rw = mysql_fetch_array($rs);

 if( !(isset($_GET['page'])) )
     $start = $rw[0] - 5;
 else
     $start = $rw[0] - ($_GET['page'] * 5);


 $res = mysql_query("SELECT * FROM apple LIMIT $start, 5") 
                              or Die("More entries coming, stay tuned!");

First we need to calculate the number of records present inside the table. Next decide onto how many items you want to display.
Based on this, subtract the actual number of records with number of items you actually want to display.

If use explicitly passes $_GET[‘page’] i.e., by clicking on the navigation or pagination numbers, then multiply the number with the number of items you want to display and subtract it with the actual number of records present inside the table.

The final step is to, pass the $start and the number till which you want to fetch from $start.

In our case, we want to show 5 items from the table apple, so we give the limit: starting from $start to 5 records after $start.

Video Tutorial: Simple / Basic Pagination / Navigation: PHP & MySQL

Complete Code
index.php

< ?php
 include_once('db.php');

 if(isset($_POST['name']))
 {
   $name = $_POST['name'];

   if(mysql_query("INSERT INTO apple VALUES('','$name')"))
  echo "Successful Insertion!";
   else
  echo "Please try again";
 }


 $rs = mysql_query("SELECT count(*) FROM apple");
 $rw = mysql_fetch_array($rs);

 if( !(isset($_GET['page'])) )
     $start = $rw[0] - 5;
 else
     $start = $rw[0] - ($_GET['page'] * 5);


 $res = mysql_query("SELECT * FROM apple LIMIT $start, 5") 
                                  or Die("More entries coming, stay tuned!");


?>

List of companies ..

< ?php while( $row = mysql_fetch_array($res) ) echo "
• $row[name]
• edit
• delete

"; ?>

• 1
• 2
• 3
• next

Error:
Notice: Undefined index: page

Make sure you have written proper code near next link in the navigation / pagination numbering..

First we check if $_GET[‘page’] is set. If not set, then we give a value of 2 to next orelse we assign the next value to $_GET[‘page’].

Also check:
GET method in action
POST method in action