PHP pagination

<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" />

    //Connect to our MySQL database using the PDO extension.
    require_once('incSearchDatabaseConnectionBySelection.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);
} ?>
    <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;">
$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";

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
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.
doctorbillTechAuthor Commented:
Could you elaborate with an example
Marco GasiFreelancerCommented:
I suggest you to read this good article by Julian Hansen: Simple, clear, effective.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Chris StanyonWebDevCommented:
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:


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

doctorbillTechAuthor Commented:
Can I have a working example please
Chris StanyonWebDevCommented:
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.
doctorbillTechAuthor Commented:
Will try when back at my pc
doctorbillTechAuthor Commented:
I have got the pages working using manual url's:


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
Chris StanyonWebDevCommented:
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

doctorbillTechAuthor Commented:
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
Chris StanyonWebDevCommented:
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:

ini_set('display_errors', 1);
doctorbillTechAuthor Commented:
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)
Chris StanyonWebDevCommented:
Hey Bill,

Glad you got it working.

The %1$s is a conversion spec that forms part of the printf() format. Think of it like a variable placeholder. Normally, to replace a string, you would just use %s, but because we want to use the same argument twice, we just need to tell it to use the 1st argument, hence the 1$. We could just have easily written it like so:

printf('<p><a href="?page=%s">Page %s</a></p>', $i, $i);

Each %s marker would be replaced by the corresponding argument. In our case we're replacing both placeholders with the same variable, so it made sense to just specify one argument and use the alternative format for the placeholder.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
doctorbillTechAuthor Commented:
Thanks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.