Home arrow MySQL arrow Page 4 - Building A Dynamic MySQL Paging Class With PHP
MYSQL

Building A Dynamic MySQL Paging Class With PHP


One handy class that Joe always keeps in his PHP toolbox is a custom MySQL recordset paging class, which he developed about 5 months ago. In this article Joe describes how he created the MySQL recordset paging class with PHP and also shows us how to use it with three cool examples!

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 49
April 04, 2002
TABLE OF CONTENTS:
  1. · Building A Dynamic MySQL Paging Class With PHP
  2. · The class
  3. · The RecNav constructor
  4. · The ShowRecs function
  5. · Using the RecNav class
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Building A Dynamic MySQL Paging Class With PHP - The ShowRecs function
(Page 4 of 6 )

ShowRecs is the one and only function contained within the RecNav class. It uses the variables setup by the constructor to interface with the MySQL database, generate the recordset pages, and also generate the paging links. Its signature looks like this:

function ShowRecs($Page=1)

It only accepts one parameter, $Page, which determines which page of the records we want to view. If $Page isn't passed in, then its given a default value of one. ShowRecs starts by creating a variable called $finalOutput, which will contain all of the template HTML code and records once the ShowRecs function finishes its work:

$finalOutput = $this->__templateHeader;

The MySQL query determines where to move the record pointer to and also how many records to return. The $Page variable is used in combination with the $__recsPerPage variable to build the query:

if($Page <= 1)

{

$Page = 1;

$query = $this->__query . " LIMIT 0, " . $this->__recsPerPage;

}

else

{ $query = $this->__query . " LIMIT " . (($Page-1) * $this->__recsPerPage) . ", " . $this->__recsPerPage; }


This query is then executed against the MySQL database, and the mysql_num_rows function is used to determine if any rows were returned:

$result = mysql_query($query);

$hasRecords = mysql_num_rows($result) == 0 ? false : true;


If $hasRecords is true then we loop through each record in the result using mysql_fetch_row. We also loop through the fields for each individual record using the mysql_num_fields function. We use str_replace to replace the placeholder values with the actual values contained within each row, like this:

if($hasRecords)

{

// At least one records returned from the query

while($row = mysql_fetch_row($result))

{

$newRow = $this->__template;

for($i = 0; $i < mysql_num_fields($result); $i++)

{ $newRow = str_replace("<| row" . $i . " |>", $row[$i], $newRow); }

$finalOutput .= $newRow;

}

}


$finalOutput will now contain the header template, $__templateHeader, as well as an instance of the $__template for each row in the result. On some occasions there may not be any rows returned from the query. To cover this, we replace the first occurance of a placeholder with the string "No records found". We also replace every other placeholder with a space by using the ereg_replace function:

else

{

// No records returned from the query

$newRow = $this->__template;

$newRow = str_replace("<| row0 |>", "No records found", $newRow);

// Replace all template tags with &nbsp;

$newRow = ereg_replace("<\| row[0-9] \|>", "&nbsp;", $newRow);

$finalOutput .= $newRow;

}


Once the resultant HTML is built, we add the template footer to the $finalOutput variable:

$finalOutput .= $this->__templateFooter;

Notice the use of intrinsic documentation throughout the RecNav class? Itís always a good idea to name your variables after what they represent, such as $finalOutput in the code above. That way if another programmer has to work with your code, he won't have any trouble identifying what each variable does.

Creating the paging links

The ShowRecs function also creates the paging links which are appended to the $finalOutput variable. First off, the ceil function is used to determine the number of pages required to display the results. Ceil rounds a decimal number up, so 3.42 becomes 4 for example. Here's the code:

// Build the recordset paging links

$numTotalRecs = mysql_num_rows(mysql_query($this->__query));

$numPages = ceil($numTotalRecs / $this->__recsPerPage);

$nav = "";


If the user's currently viewing any page after the first page then we can include a link to the previous page:

// Can we have a link to the previous page?

if($Page > 1)

$nav .= "<a href='$PHP_SELF?page=" . ($Page-1) . "'>&lt;&lt; Prev</a> |";


Next we have the main counter loop. This loop simply outputs the page number. If the value of the loop is the same number as the current page then that page number is output as bold. If not, the page number is linked using the $PHP_SELF variable and loop counter:

for($i = 1; $i < $numPages+1; $i++)

{

if($Page == $i)

{

// Bold the page and dont make it a link

$nav .= " <b>$i</b> |";

}

else

{

// Link the page

$nav .= " <a href='$PHP_SELF?page=$i'>$i</a> |";

}

}


The RecNav class is contained within a file called class.recnav.php. When you include this class in another PHP script and instantiate the RecNav class, then the value of $PHP_SELF will be the value of the script including class.recnav.php.

Once all of the pages are done, we include a "Next" link if possible:

// Can we have a link to the next page?

if($Page < $numPages)

$nav .= " <a href='$PHP_SELF?page=" . ($Page+1) . "'>Next &gt;&gt;</a>";


If the user is viewing a page that isn't the last page, then there's going to be an extra | character in the $nav variable, for example:

<< Prev | 1 | 2 | 3 |

We use the ereg_replace function to remove the | if it exists:

// Strip the trailing pipe if there is one

$nav = ereg_replace("\|$", "", $nav);


Lastly, we append the page navigation variable $nav to $finalOutput. We also include some HTML formatting to make the output look a bit cleaner:

$finalOutput .= "<div align='right'><font face='verdana' size='1'><br>Pages: $nav</font></div>";

return $finalOutput;

}


That's all there is to our RecNav class. Letís now take a look at how we can use it to generate flexible, HTML based recordset pages.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 Ė More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials