Link to home
Create AccountLog in
Avatar of doctorbill
doctorbillFlag for United Kingdom of Great Britain and Northern Ireland

asked on

PHP pagination

<html>
<head>
<title>Inventas Sites Visits</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
     <link href="css/layout.css" rel="stylesheet" type="text/css" />
        <link href="css/menu.css" rel="stylesheet" type="text/css" />

    <?php 
    //Connect to our MySQL database using the PDO extension.
    require_once('incSearchDatabaseConnectionBySelection.php'); ?>
        
    <?php
    
$space = "&nbsp;";
$break= "</br>";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} ?>
</head>
    <body align=center>
        <?php require_once('incMenu.php'); ?> 
        <br />
        <h1>Inventas Database Search Results - All Records</h1>
        <div style= "text-align: left; margin-left:300px; width:600px;">
    <?php
$sql = "SELECT sites_id, sites_name, sites_email, sites_company, sites_representative, sites_comments, sites_todo, sites_datevisited, sites_return FROM inventassites";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo '<font color="blue">' . "id:" . '<font color="purple">' .$space.$row["sites_id"]. " - Name: " . $row["sites_name"]. " email address: " .  $row["sites_email"]. " Company: " . $row["sites_company"]. '<font color="red">'.$break." Representative: " . $row["sites_representative"]. '<font color="green">' .$break."Comments: " . $row["sites_comments"]. '<font color="purple">' .$break."ToDo: " . $row["sites_todo"]. " DateVisited: " . $row["sites_datevisited"]. " Return Visit: " . $row["sites_return"]. "<br>". "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>
        </div>
    
    </body>
</html>

Open in new window


I have been trying to add pagination to the above code. I cannot seem to get the pagination to work. Could someone please give me a tutorial on this or some code to try on my recordsets
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Pagination in MySQL is simple, look at the LIMIT and OFFSET clause.

10 records per page?
LIMIT recordsperpage

Nth page instead of first page?
OFFSET (pageno-1)*recordsperpage

That's it, mostly. Your records will only be for that page. You only need to specify the recordsperpage and pageno numbers.
Avatar of doctorbill

ASKER

Could you elaborate with an example
I suggest you to read this good article by Julian Hansen: https://www.experts-exchange.com/articles/12238/Pagination-with-PHP-and-Mysql.html. Simple, clear, effective.
Hey BIll,

As Olaf alluded to, the key to pagination is passing in a LIMIT clause to your SELECT query:

SELECT * FROM yourTable LIMIT startRow, NumberOfRows

Now the Number of Rows is likely to remain static throughout your applications - each page will need to show the same number of records, for example 10. The dynamic part is the starting row (or Offset), which is created by multiplying the page number you want with the number of records per page. For example:

$recordsPerPage = 10;
$pageNum = 3;
$start = $pageNum * $recordsPerPage;

SELECT * FROM yourTable LIMIT $start, $recordsPerPage;

In the example above, your query will fetch 10 records, starting at record number 30.

Now you obviously wouldn't hardcode the page number as that's usually passed in via a link:

mypage.php?page=1
mypage.php?page=2
mypage.php?page=3

You'll need to do some additional maths because the records are numbered using zero as a base, so page 1 needs to start at record 0, page 2 needs to start at 10 for example. This is easy to do by subtracting 1 from the page passed it

$recordsPerPage = 10;
$pageNum = isset($_GET['page']) ? $_GET['page'] : 1;
$start = ($pageNum - 1) * $recordsPerPage;

To build your pagination links in the first place, you'll need to retrieve the total number of records in your query and divide that by the number of records per page. We use the ceil() function to round up the fractions. Something along these lines:

$numberOfPages = ceil($totalRecords / $recordsPerPage);

for ($i=1; $i <= $numberOfPages; $i++) {
    printf('<p><a href="?page=%1$s">Page %1$s</a></p>', $i);
}

Open in new window

Can I have a working example please
Hey Bill,

Is there anything in my example that doesn't make sense. It's up to you to implement it into your own code - just copying and pasting a solution won't really help you in the long run as you won't understand what your own code is doing.

Here's a starting point:

$recordsPerPage = 10;
$pageNum = isset($_GET['page']) ? $_GET['page'] : 1;
$start = ($pageNum - 1) * $recordsPerPage;

$sql = "SELECT sites_id, sites_name, sites_email, sites_company, sites_representative, sites_comments, sites_todo, sites_datevisited, sites_return FROM inventassites LIMIT $start, $recordsPerPage";

Open in new window

Just call you pages with the ?page=x querystring.
Will try when back at my pc
I have got the pages working using manual url's:
?page=1
?page=2

etc

This works fine on my recordset and the results are 1-10 records on page1, 11-20 on page 2 etc
I am just having a problem with this part:
$numberOfPages = ceil($totalRecords / $recordsPerPage);

for ($i=1; $i <= $numberOfPages; $i++) {
    printf('<p><a href="?page=%1$s">Page %1$s</a></p>', $i);
}
This is just printed as text in the page. How do I define these variables
Hey Bill,

What do you mean - printed as text in the page. That code will work out how many pages you need and then echo out a link for each one, something like this:

<p><a href="?page=1">Page 1</a></p>
<p><a href="?page=2">Page 2</a></p>
<p><a href="?page=3">Page 3</a></p>

For it to work, you'll need to run a query to calculate the $totalRecords:

$count = $conn->query("SELECT COUNT(1) as totalRecords FROM inventassites");
$result = $count->fetch_object();
$totalRecords = $result->totalRecords;

Open in new window

I Tried this:
<?php $sql2 = 'SELECT COUNT(*) FROM inventassites';
$totalRecords = executeQuery($sql2);
            $numberOfPages = ceil($totalRecords / $recordsPerPage);

for ($i=1; $i <= $numberOfPages; $i++) {
    printf('<p><a href="?page=%1$s">Page %1$s</a></p>', $i);
} ?>

No printout on page
What is executeQuery() ?

Have another look over the code I've just psoted - that's what you need.

To help with all of this, make sure you have error reporting turned on at the start of your script:

error_reporting(E_ALL);
ini_set('display_errors', 1);
Perfect - all now working
I must say your explainations and code are so very clear and concise
One last thing - what is the %1$s (specifically the $s)
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Thanks all