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:
1Google USA
2Apple USA
3Microsoft USA
4Oracle USA
10IBM
11HP
12Symantec
13Adobe
14Cisco
15McAfee
16Red Hat
17Sun Microsystems
18Intel
19Salesforce
20Facebook
21Technotip
22MindTree
23Tate Consultancy Ser
24Cognizant
25Citigroup
26Maestro
27Visa
28KingFisher
29HDFC
30ICICI
31SBI
32SBM
33Twitter
34LinkedIn
35BlueDart
36VRL
37Zappos
38FlipKart
39Amazon
40Yahoo!
41ebay
42PayPal
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
1
| mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date ASC LIMIT $start, 5; |
mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date ASC LIMIT $start, 5;
1
| mysql> SELECT * from apple [ WHERE some_condition ] ORDER BY date DESC 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.
1
2
3
4
5
6
7
8
9
10
11
| $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!"); |
$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
[youtube https://www.youtube.com/watch?v=06o4C32B6Bg]
Complete Code
index.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
| < ?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!");
?>
<html>
<head>
<style type="text/css">
li { list-style-type: none; display: inline; padding: 10px; text-align: center;}
// li:hover { background-color: yellow; }
</style>
</head>
<body>
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
<h1>List of companies ..</h1>
<ul>
< ?php
while( $row = mysql_fetch_array($res) )
echo "<li>$row[name]
<li><a href='edit.php?edit=$row[id]'>edit</a></li>
<li><a href='delete.php?del=$row[id]'>delete</a></li><br />";
?>
</ul>
<ul>
<li><a href="index.php?page=1">1</a></li>
<li><a href="index.php?page=2">2</a></li>
<li><a href="index.php?page=3">3</a></li>
<li><a href="index.php?page=<?php
if(isset($_GET['page']))
$next = $_GET['page'] +1;
else
$next=2;
echo $next; ?>">next</a>
</li>
</ul>
</body>
</html> |
< ?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!");
?>
<html>
<head>
<style type="text/css"> li { list-style-type: none; display: inline; padding: 10px; text-align: center;}
// li:hover { background-color: yellow; }
</style>
</head>
<body>
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
<h1>List of companies ..</h1>
<ul>
< ?php
while( $row = mysql_fetch_array($res) ) echo "<li>$row[name] <li><a href='edit.php?edit=$row[id]'>edit</a></li> <li><a href='delete.php?del=$row[id]'>delete</a></li><br />";
?>
</ul>
<ul>
<li><a href="index.php?page=1">1</a></li>
<li><a href="index.php?page=2">2</a></li>
<li><a href="index.php?page=3">3</a></li>
<li><a href="index.php?page=<?php if(isset($_GET['page'])) $next = $_GET['page'] +1; else $next=2; echo $next; ?>">next</a>
</li>
</ul>
</body>
</html>
Error:
Notice: Undefined index: page
Make sure you have written proper code near next link in the navigation / pagination numbering..
1
2
3
4
5
6
7
8
9
10
| <li>
<a href="index.php?page=<?php
if(isset($_GET['page']))
$next = $_GET['page'] +1;
else
$next=2;
echo $next;
?>">next</a>
</li> |
<li>
<a href="index.php?page=<?php if(isset($_GET['page'])) $next = $_GET['page'] +1; else $next=2; echo $next;
?>">next</a>
</li>
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