Building A Dynamic MySQL Paging Class With PHP

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