Simple / Basic Pagination / Navigation: PHP & MySQL

Advertisement:

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

1
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;

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!");

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


You need to a flashplayer enabled browser to view this YouTube video

YouTube Link: http://www.youtube.com/watch?v=06o4C32B6Bg [Watch the Video In Full Screen.]



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>

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>

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

10 thoughts on “Simple / Basic Pagination / Navigation: PHP & MySQL”

  1. @Raihan, If you have problem with pagination using above code, then let me know.

    You can ask us on our forum too, so that everything will be on one page there.

  2. can you explain your logic here:
    if( !(isset($_GET[‘page’])) )
    $start = $rw[0] – 5;
    else
    $start = $rw[0] – ($_GET[‘page’] * 5);

    you have 42 records and you want to display 5 items per page
    so ” $start = $rw[0] – 5; ” so the initial value of $start is 37? should it be 0?

        1. @oranger, The purpose of Pagination is to show only partial data to the user. If he/she wants to look at more data, they can click on the pagination links!

          @jack, We’re taking it from 5 less than the actual number of data.
          Not from 0. Because, we want to show the user the latest entries first.
          If you’re aim is to show the same old data in the front page all the time, then you must start from 0.

          Our aim is to show fresh data first, so, start fetching the latest entries first.

          Hope it’s clear now.

  3. Thanks for the code technotip. Really great.
    I have 27 records in my db. The codde displays the first 20 correctly…but when i click next i get this error “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-5, 5’ at line 1”

    1. @oranger, It looks so obvious that, if the record needs to be fetched from -5, which doesn’t exist, it throws some errors.

      Check if the $start is negative, is so, display some message or handle it according to your needs and data availability.

Leave a Reply to oranger Cancel reply

Your email address will not be published. Required fields are marked *