Simple Pagination with the FileMaker PHP API
During one of my sessions at the recent 2010 FileMaker Developer Conference in San Diego I was asked about the pagination/navigation on a search results page that is generated by the FileMaker PHP API. They were referring to the links you might see at the top/bottom of the search results page like this:
First | Previous | Record 21 – 40 of 52 Next | Last
which are generated dynamically and use the equivalent of conditional formatting in FileMaker to display only the necessary elements. For example on the first page of the search results there is no Previous page nor can you go to the First page as you are already on it; likewise with the last page there is no Next page and no Last page, so these should not appear depending on the context of the current page.
I finally found some time to put together a simple example of how you can generate these using the FileMaker PHP API.
As with FileMaker when you perform a search via PHP/Custom Web Publishing you will generate a found set of records (or no matching records). With a large found set of records you might want to present these to the user in chunks, for example you could present 5/10/20 records per page and allow the user to navigate to the next page. This is similar to how most search engines on the web work these days. Google for example defaults to showing 10 results per page. With the FileMaker PHP API you use the setRange() method to request only part of the found set of records by passing in 2 paremeters: the first parameter is the number of records to skip past and the second parameter is the maximum number of records to return.
Say you do a search and you find 52 matching records. Using a $max value of 20 you will get records 1-20 on the first page, records 21 to 40 on the second page, and records 41 to 52 on the third (and last) page. On the first page we are skipping 0 records, on the second page we are skipping 20 records and on the third (and last) page we are skipping 40 records.
Here’s an example of how to use the setRange method:
// Set a Max value. Paging 20 records at a time
$max = 20;
$skip = $_GET['skip'];
if(!isset($skip)) { $skip = 0; }
$request->setRange($skip, $max);Here I’ve hardcoded the $max value to 20 which means I’ll only ever get back 20 records at the most per page. For the skip value I’m using the $skip variable and if this hasn’t already been set I’m setting it to 0, which is typically done on the first page of search results. Otherwise this will be passed as a parameter in the URL and the php script will check to see if this GET variable exists. Now we need to perform the find and check for a found set of records:
// Perform the Find
$result = $request->execute();if (FileMaker::isError($result)) {
if ($result->code = 401) {
$errorMessage = "There are no Contacts that match that request: " . ' (' . $result->code . ')';
} else {
$errorMessage = "Contacts Find Error: " . $result->getMessage() . ' (' . $result->code . ')';
}} else {If there are matching records we then generate some variables that will be used in the First, Previous, Next and Last links, as well as calculating the number of records found and how many were “fetched” on the current page:
If you use the previous example of 52 matching records here are the values of these variables for each of the 3 pages of search results:
- Page 1 (records 1-20): $skip = 0, $max = 20, $found = 52, $ fetchcount = 20, $prev = -20, $next = 20, $lastskip =42, $firstrecord = 1
- Page 2 (records 1-20): $skip = 20, $max = 20, $found = 52, $ fetchcount = 20, $prev = 0, $next = 40, $lastskip =42, $firstrecord = 21
- Page 3 (records 1-20): $skip = 40, $max = 20, $found = 52, $ fetchcount = 12, $prev = 20, $next = 60, $lastskip =42, $firstrecord = 41
I’m also using a pipe character as a separator between the links as well which is stored in the $sepbar variable. Now we’re ready to generate the links within the HTML part of the php page: Here’s the code for the “First” link:
<?php
if ($skip != 0) {echo '<a href="?skip=0">First</a>'.$sepbar;
}
?>As we only want to show the First link on every page except for the first page of search results we are checking the value of the $skip variable. If $skip is not equal to 0 then we are not on the first page of search results so we can show this link, along with the pipe character to give it some space between the links.
For the “Previous” link we use:
<?php
if ($prev >= 0) {
echo '<a href="?skip='.$prev.'">Previous</a>'.$sepbar;
}
?>
Here we only want to show the Previous link if there is a previous page of search results, so we can check the contents of the $prev variable which will only equal 0 on the first page of the search results.
For the “Next” link we use:
<?php
if (($skip + $max) < $found) {
echo '<a href="?skip='.$next.'">Next</a>'.$sepbar;
}
?>Here we need to check that there actually are subsequent pages of search results, which we do by checking that the total of the $skip and $max variables is less than the total found set of records.
Finally for the “Last” link we use:
<?php
if (($skip + $fetchcount) < $found) {
echo '<a href="?skip='.$lastskip.'">Last</a>';
}
?>
Here we need to test that we are not on the last page of the search results which we do by checking that the total of the $skip and $fetchcount variables is less than the total found set of records.
If you want to display the total number of found records along with the records that you are currently viewing (e.g. “Record 21 – 40 of 52”) you can use:
Record <?php echo $firstrecord; ?> - <?php echo $lastrecord; ?> of <?php echo $found; ?>
I’d like to credit Sonja Froyen’s article Custom Web Publishing: Paginate Your Results which I used as a starting point for some of these links (no need to reinvent the wheel here). If you Google “PHP Pagination” there are thousands of other articles on different ways to generate the pagination links, including variations which show how to include the search results page numbers like Google (e.g. “Previous 1 2 3 4 5 6 7 8 9 10 11 Next”. Most of these reference MySQL as the data source but it’s relatively easy to swap the references to the FileMaker PHP API.
As some of this code won’t make sense on it’s own here’s the full php page with the HTML (remember this is a simple page that finds all of the Contacts in the DevCon sample file):
<?phprequire_once 'FileMaker.php';
require_once 'connections/INT002.php';
}
}?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>
FileMaker DevCon INT002 Contacts
</title>
<link rel="stylesheet" type="text/css" media="screen" href="css/glass_grey.css">
<style type="text/css">
form#auth label.error { display: none;
margin-top: 5px;
color: red;}
.appropriateError{
color: red;
}
.emphasise{
color: red;
}
</style>
</head>
<body>
<div id="container">
<!-- HEADER -->
<div id="header">
<h1>
FileMaker DevCon INT002 Contacts
</h1>
</div></div>
<table>
<thead>
<tr>
<th>
Customer ID </th>
<th>
First Name </th>
<th>
Last Name </th>
<th>
Country </th>
<th>
Phone Mobile </th>
</tr>
</thead>
<tbody>
<?php
$recnum = 1;
foreach($result->getRecords() as $contacts_search_row){
$rowclass = ($recnum % 2 == 0) ? "table_row" : "alt_row";
$recid = $contacts_search_row->getRecordId();
$pos = strpos($recid, "RID_!");
if ($pos !== false) {
$recid = substr($recid,0,5) . urlencode(substr($recid,strlen("RID_!")));
}
?>
<tr>
<td><a href='<?php echo "contactdetails.php?recid=$recid";?>'><?php echo nl2br( $contacts_search_row->getField('_kp_ContactID'))?></a></td>
<td>
<?php echo nl2br( $contacts_search_row->getField('NameFirst'))?> </td>
<td>
<?php echo nl2br( $contacts_search_row->getField('NameLast'))?> </td>
<td>
<?php echo nl2br( $contacts_search_row->getField('Country'))?> </td>
<td>
<?php echo nl2br( $contacts_search_row->getField('PhoneMobile'))?> </td>
</tr>
<?php $recnum++; } /* foreach record */?>
</tbody>
</table>
</div>
</div>
</body>
</html><!– Navigation Menu –>
<?php include_once ‘navigation.php’ ?><!– PAGE BODY –>
<div>
<?php
if ($errorMessage != ”) {
echo $errorMessage;
die;
}
?>
</div>
<div id=”content”>
<h1>
Contacts List </h1>
Click on the Customer ID to view the full Contact details
<!– Display record list page navigation controls –>
<div>
<?php
if ($skip != 0) {
echo ‘<a href=”?skip=0″>First</a>’.$sepbar;
}
?>
<?php
if ($prev >= 0) {
echo ‘<a href=”?skip=’.$prev.'”>Previous</a>’.$sepbar;
}
?>
Record <?php echo $firstrecord; ?> – <?php echo $lastrecord; ?> of <?php echo $found; ?>
<?php
if (($skip + $max) < $found) {
echo ‘<a href=”?skip=’.$next.'”>Next</a>’.$sepbar;
}
?>
<?php
if (($skip + $fetchcount) < $found) {
echo ‘<a href=”?skip=’.$lastskip.'”>Last</a>’;
}
?>
$layouts = $fm->listLayouts();
if(FileMaker::isError($layouts)) {
// FileMaker PHP API Error — Alert User.
$errorMessage = “FileMaker PHP Error: ” . $layouts->getMessage();
} else {
// Find all Contact records
$request = $fm->newFindAllCommand(‘WebContacts’);
// Set a Max value. Paging 20 records at a time
$max = 20;
$skip = $_GET[‘skip’];
if(!isset($skip)) { $skip = 0; }
$request->setRange($skip, $max);
// Perform the Find
$result = $request->execute();
if (FileMaker::isError($result)) {
if ($result->code = 401) {
$errorMessage = “There are no Contacts that match that request: ” . ‘ (‘ . $result->code . ‘)’;
} else {
$errorMessage = “Contacts Find Error: ” . $result->getMessage() . ‘ (‘ . $result->code . ‘)’;
}
} else {
// Get the found records and setup page navigation links
$records = $result->getRecords();
$found = $result->getFoundSetCount();
$fetchcount = $result->getFetchCount();
// $totalpages = ceil($found / $max);
$prev = $skip – $max;
$next = $skip + $max;
if(($skip + $max) > $found) {$next = $skip; }
$lastskip = $found – $max;
$firstrecord = $skip + 1;
if ($fetchcount == $max) {
$lastrecord = (($firstrecord + $fetchcount) – 1);
} else {
$lastrecord = ($skip + $fetchcount);
}
$sepbar = ” | “;
// Get the found records and setup page navigation links
$records = $result->getRecords();
$found = $result->getFoundSetCount();
$fetchcount = $result->getFetchCount();
$totalpagesceil = ceil($found / $max);
$totalpagesfloor = floor($found / $max);
$prev = $skip – $max;
$next = $skip + $max;
if(($skip + $max) > $found) {$next = $skip; }
if($totalpagesceil == $totalpagesfloor) {
$lastskip = ($totalpagesceil – 1) * $max;
} else {
$lastskip = $totalpagesfloor * $max;
}
$firstrecord = $skip + 1;
if ($fetchcount == $max) {
$lastrecord = (($firstrecord + $fetchcount) – 1);
} else {
$lastrecord = ($skip + $fetchcount);
}
$sepbar = ” | “;
}
}
