Simple / Basic Pagination / Navigation: PHP & MySQL


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


[youtube https://www.youtube.com/watch?v=06o4C32B6Bg]

YouTube Link: https://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

      

Related posts:

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

  2. @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.

    Reply

  3. 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?

    Reply

        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.

          Reply

  4. 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”

    Reply

    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.

      Reply

Leave a Reply

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