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 = " ";
$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>
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
PHPJavaScriptMySQL Server
Last Comment
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:
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);
}
ASKER
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:
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";
ASKER
Will try when back at my pc
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('<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
?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:
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;
ASKER
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
<?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);
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);
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)
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks all
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.