Sample Web Designs

User submitted Webdesign Articles and Tutorials

MySQL results - Page numbering

By James Middleton - 26th of June 2007

---

When I first using PHP and MySQL I struggled to find a decent working pagination script 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 codlings 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 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...

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

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

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

Good luck!