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

Olaf Doschke

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.

doctorbill

ASKER

Could you elaborate with an example

Marco Gasi

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.

Chris Stanyon

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

doctorbill

ASKER

Can I have a working example please

Chris Stanyon

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.

doctorbill

ASKER

Will try when back at my pc

doctorbill

ASKER

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('<a href="?page=%1$s">Page %1$s</a>', $i);
}
This is just printed as text in the page. How do I define these variables

Chris Stanyon

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:

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

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

doctorbill

ASKER

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

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

No printout on page

Chris Stanyon

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

doctorbill

ASKER

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

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

doctorbill

ASKER

Thanks all