When I first using PHP and MySQL I struggled to find a decent working pagination method that would split results from a MySQL statement and spread them across different numbered pages. If I remember rightly, I tried dozens of tutorials and code snippets and found them all to be either very complex and long winded or very difficult to tweak.
Nowadays, I am a skilled enough PHP/MySQL developer to create (or partially create) my own coding without being to much of a ‘copy, paste n tweak king’. Only the other day I revisited the subject of pagination and decided to have a go at creating a script based on a more complex model that I had previously borrowed from a tutorial site.
The following script does borrow a little from this example http://www.phpfreaks.com/tutorials/73/0.php, but I have changed it substantially to allow for both easy integration and (in my opinion) a better result.
The PHP pagination code
Firstly we’ll start with connecting to a database…
<?php
$hostname=’localhost’;
$user=’username’;
$pass=’password’;
$dbase=’database’;
$connection = mysql_connect(”$hostname” , “$user” , “$pass”) or die (”Can’t connect to MySQL”);
$db = mysql_select_db($dbase , $connection) or die (”Can’t select database.”);
Now, we’ll add a few variables. This code will need altering to suit your current setup…
/// START of MySQL results – Page numbering
/// Variables
$WHERE = “WHERE county = ‘Cornwall’ AND accom = ‘Hotel’ “; /// Your sql statement
$max_results = 5; /// Number of results per page
Now for the messy stuff. The following code is a collect of MySQL statements and result handlers…
if(!isset($_GET['pg'])){ $pg = 1; } else { $pg = $_GET['pg']; }
$from = (($pg * $max_results) – $max_results);
/// Count total
$totals = mysql_result(mysql_query(”SELECT COUNT(*) as Num FROM countries $WHERE “),0);
$total_pgs = ceil($totals / $max_results);
/// Page limiter & result builder
$sql = “SELECT * FROM countries $WHERE LIMIT $from, $max_results”;
$result1 = mysql_query($sql); $num_sql = mysql_num_rows ($result1);
Now that we have connected to our database, established a few variables and sent out the request search parameters to our database tables, let’s display the results. We’ll start with amount of results and page positioning.
echo “Results: $totals <br>”;
echo “Viewing page $pg of $total_pgs<br>”;
We will need a script to generate the pagination, i.e. Next, Prev, etc. For the sake of making this pagination mobile, instead of ‘echoing out’ the next, prev and numbers, I will put them into a string and call it ‘$paginator’…
// Build paginator
if($pg > 1){ $prev = ($pg – 1); // Previous Link
$paginator =”<a href=”".$_SERVER['PHP_SELF'].”?pg=$prev”>”Previous page</a>”; }
for($i = 1; $i <= $total_pgs; $i++){ /// Numbers
if(($pg) == $i) { $paginator .= “<i>$i</i> “; } else {
$paginator .=”<a href=”".$_SERVER['PHP_SELF'].”?pg=$i”>$i</a> “; }}
if($pg < $total_pgs){ $next = ($pg + 1); // Next Link
$paginator .=”<a href=”".$_SERVER['PHP_SELF'].”?pg=$next”>”Next page.”</a>”; }
That’s all of the hard work out of the way. Now let’s put the finishing touches to the paginator – display the results and show the next, prev and numbers…
echo “$paginator<br><br>”;
/// Display results
if ($num_sql > 0 ) {$i=0;
while ($i < $num_sql) {
$holsite = mysql_result($result1,$i,”holsite”);
echo “$holsite<br>”;
++$i;}}
echo “<br>$paginator”;
?>
Done. Now we have pagination of MySQL results.
Page numbering PHP example
Here’s the entire PHP script:
<?php
$hostname=’localhost’;
$user=’username’;
$pass=’password’;
$dbase=’database’;
$connection = mysql_connect(”$hostname” , “$user” , “$pass”) or die (”Can’t connect to MySQL”);
$db = mysql_select_db($dbase , $connection) or die (”Can’t select database.”);
/// Variables
$WHERE = “WHERE county = ‘Cornwall’ AND accom = ‘Hotel’ “; /// Your sql statement
$max_results = 5; /// Number of results per page
if(!isset($_GET['pg'])){ $pg = 1; } else { $pg = $_GET['pg']; }
$from = (($pg * $max_results) – $max_results);
/// START of MySQL results – Page numbering
/// Count total
$totals = mysql_result(mysql_query(”SELECT COUNT(*) as Num FROM countries $WHERE “),0);
$total_pgs = ceil($totals / $max_results);
/// Page limiter & result builder
$sql = “SELECT * FROM countries $WHERE LIMIT $from, $max_results”;
$result1 = mysql_query($sql); $num_sql = mysql_num_rows ($result1);
echo “Results: $totals <br>”;
echo “Viewing page $pg of $total_pgs<br>”;
// Build paginator
if($pg > 1){ $prev = ($pg – 1); // Previous Link
$paginator =”<a href=”".$_SERVER['PHP_SELF'].”?pg=$prev”>”Previous page</a>”; }
for($i = 1; $i <= $total_pgs; $i++){ /// Numbers
if(($pg) == $i) { $paginator .= “<i>$i</i> “; } else {
$paginator .=”<a href=”".$_SERVER['PHP_SELF'].”?pg=$i”>$i</a> “; }}
if($pg < $total_pgs){ $next = ($pg + 1); // Next Link
$paginator .=”<a href=”".$_SERVER['PHP_SELF'].”?pg=$next”>”Next page.”</a>”; }
echo “$paginator<br><br>”;
/// Display results
if ($num_sql > 0 ) {$i=0;
while ($i < $num_sql) {
$holsite = mysql_result($result1,$i,”holsite”);
echo “$holsite<br>”;
++$i;}}
echo “<br>$paginator”;
/// END of MySQL results – Page numbering
?>
Good luck!
