List View / Paging through Records

Use the sample app to create about 20 more landlord records. We'll enable pagination so that the list view displays a fixed number of records regardless how big the database becomes.

To save the effort of entering dummy data, we'll display 5 records per page. This number can be easily changed to more reasonable values, such as 10 or 20.

This is the original query to get all the landlords:

select landlords.*, persons.*
from landlords, persons
where landlords.personid=persons.personid
order by fname,lname

We'll be running the query twice with some variations. Record sorting is dropped for the first pass; pagination and sorting is added to the second pass.

The trick here is to write the query only once.

$query="select landlords.*,persons.*
from landlords, persons
where landlords.personid=persons.personid ";

$rs=sql_query($query,$db); // 1st pass
$count=sql_affected_rows($db, $rs);

$page=$_GET['page']+0;
if ($page<0) $page=0; //lower bound
$perpage=5;
$maxpage=ceil($count/$perpage)-1;
if ($maxpage<0) $maxpage=0; //lower bound
if ($page>$maxpage) $page=$maxpage; //upper bound

$start=$page*$perpage; //starting record position
$query.=" order by ffname limit $start, $perpage";

$rs=sql_query($query,$db); //2nd pass

If the records do not fit in one page, we can display the paging links:

if ($maxpage>1){
?>
<div style="padding:10px 0;">
<a onclick="...">« Prev</a>
|
Page <?echo $page+1;?> of <?echo $maxpage+1;?>
|
<a onclick="...">Next »</a>
</div>
<?
}//paging

In the above snippet, « gives a left arrow symbol («); » gives a right arrow symbol (»);. The page numbers are 0-based, so they need to be increamented by one when displayed.

Before we fill out the blanks in the Prev and Next links, let's take a look at the pieces we have so far, and how they fit in the LCHH architecture.

First there is a list view container, lv0 that serves as a Loader. Clicking on the menu icon is handled by the showview Client Handler, which in turn, triggers the listlandlords function via the slv0 Server Handler. The listlandlords function outputs the Content that's injected back to the loader lv0.

Loader, Handler, Handler, Content

The paging links in the content invoke more client-side events that keep the LCHH cycle running.

For now, we can reuse the lv0 container and the slv0 message. Since the page calculation in listlandlords is already getting the page parameter, we can set the Next page link to the following:

In the sample application, document.appsettings.codepage is evaluated to myservices.php. This value is set in settings.php and can be retrieved on the server side as well, using the global $codepage variable:

<?
global $codepage;
?>
<a
onclick="ajxpgn('lv0','<?echo $codepage;?>?cmd=slv0
&page=<?echo $page+1;?>');">
Next »
</a>

Using lv0 to inject the content is not the greatest idea. Later we'll add a search box in the list view. When each keystroke reloads the content that includes the search box, the typing is interrupted. In addition, the script block at the end of listlandlords is repeated loaded for no reason.

Let's define a sub-loader:

<?php
function listlandlords(){
global $db;
$mode=$_GET['mode'];
if ($mode=='embed'){
?>
<div class="section">
<div id="landlordlist">
<?
} //embed
// ...
if ($mode=='embed'){
?>
</div>
</div>
<script>
//...
</script>
<?
} //embed
} //function

Now add the embed parameter to the client handler:

<a
onclick="ajxpgn('lv0',document.appsettings.codepage + '?cmd=slv0
&page=<?echo $page+1;?>
&mode=embed');">
Next »
</a>