Detail View / Detail Lists

So far, we've implemented the interface for creating and modifying record in a single table. You can easily add new fields to the landlords table such as address, date of birth and so on. This doesn't change the structure of the application and therefore doesn't add to programming complexity.

Next we'll add a "Phone Numbers" section in the landlord view, so that a list of phone numbers can be maintained.

The first step is to design the database table. Since a landlord can have zero, one or many associated phone numbers, we'll be using a one-to-many relationship. This is accomplished by having the primary key of the main table as the foreign key in the sub table.

For example, the primary key of the landlords table is llid. We add the llid field to the landlordphonenumbers field:

lpid primary key, auto increment, unsigned integer
llid unsigned integer, indexed
phone varchar(255)

Note the table name follows the convension of main record's name plus sub record's name in plural form.

Now populate this table with some test data, so that at least three numbers are associated with a landlord that's already in the database.

Add a Phone Numbers section in the landlord view:

//showlandlord.php
function showlandlord($llid=null){
// ...
?>
<div class="sectionheader">Phone Numbers</div>
<table>
<?
$query="select * from landlordphonenumbers
where llid=$llid order by lpid";
$rs=sql_query($query,$db);
while ($myrow=sql_fetch_array($rs)){
$phone=$myrow['phone'];
?>
<tr><td><?echo $phone;?></td>
<td><a>[x]</a></td>
</tr>
<?
} // while
?>
<tr><td>
<input id="landlordphone_new_<?echo $llid;?>">
</td><td>
<button onclick="addlandlordphone(<?echo $llid;?>);">
Add Phone
</button>
</td></tr>
</table>
<?
// ...
}

Reload the page and you should see the 3 phone numbers that are added to the database are displayed in the landlord view. This should provide you a basis to visualize the rest of the functionality.

What do you think should happen when the user enters a number in the input field and click on the "Add Phone" button? Well, a new row of the added number should show up in addition to the existing 3 numbers. And when the [x] button is clicked on, the phone number should be removed from the list. Now without writing any code, think about how the above two interactions, namely insertion and deletion, could be implemented.

The button should invoke a JavaScript function that gathers the new number. The phone number is then handled by a server-side function that adds the number to the database. Upon completion, the new list of numbers should be displayed in the landlord view.

Read the last sentence again. Also read showlandlord.inc.php again.

You must realize that the current implementation of showlandlord has two issues. First of all, there is nowhere to inject the server response. Second, the phone number insertion function has no display function to piggy back to.

The first issue can be resolved by adding a container:

<div class="sectionheader">Phone Numbers</div>
<div id="landlordphonenumbers_<?echo $llid;?>">
...
</div>

Take a look at what we did in updatelandlord server handler. We called an independent showlandlord function. Similar we could factor out the code for displaying phone numbers and store it in a separate file.

Create a file listlandlordphonenumbers.inc.php in the icl folder.

<?php
function listlandlordphonenumbers($llid){
global $db;
$query="select * from landlordphonenumbers
where llid=$llid order by lpid";

$rs=sql_query($query,$db);

while ($myrow=sql_fetch_array($rs)){
$phone=$myrow['phone'];
?>
<tr><td><?echo $phone;?></td>
<td><a>[x]</a></td>
</tr>
<?
} // while
?>
<tr><td>
<input id="landlordphone_new_<?echo $llid;?>">
</td><td>
<button onclick="addlandlordphone(<?echo $llid;?>);">
Add Phone
</button>
</td></tr>
</table>
<?
}

The list container still stays in showlandlord.inc.php:

<?php
include 'icl/listlandlordphonenumbers.inc.php';
function showlandlord($llid=null){
// ...
?>
<div class="sectionheader">Phone Numbers</div>
<div id="landlordphonenumbers_<?echo $llid;?>">
<? listlandlordphonenumbers($llid); ?>
</div>
<?
// ...
}

The steps to follow are straightforward. We add a client-side handler in landlords.js:

addlandlordphone=function(llid){
var phone=gid('landlordphone_new_'+llid).value;
phone=encodeHTML(phone);
ajxpgn('landlordphonenumbers_'+llid,
document.codepage.appsettings+
'?cmd=addlandlordphone&llid='+llid+
'&phone='+phone);
}

In myservices.php, add a clause to the switch:

case 'addlandlordphone':
include 'icl/addlandlordphone.inc.php';
addlandlordphone();
break;

Implement the insertion function in addlandlordphone.inc.php:

<?php
include 'icl/listlandlordphonenumbers.inc.php';
function addlandlordphone(){
$llid=GETVAL('llid');
$phone=GETVAR('phone');

global $db;
$query="insert into landlordphonenumbers (llid,phone)
values($llid, '$phone')";

sql_query($query,$db);
listlandlordphonenumbers($llid);
}

At this point you should see a pattern. The content is first displayed in a tab, or any kind of DIV container. The content contains components such as input fields that collect data from the user. A trigger such as a button or link invokes a JavaScript function that assembles the user input and sends it to a server-side switch. The switch clause further includes and calls the serverside handler. If the handler modifies the data, it also includes the displaying function and piggyback the updated result that's injected back to the original container. This flow is the bread and butter of building web applications; it is used extensively in Gyroscope.

Let's review this process again by adding deletion of phone numbers from the list.

First, we add the "trigger" for deleting a record.

In listlandlordphonenumbers, add an event to the [x] button:

while ($myrow=sql_fetch_array($rs)){
$lpid=$myrow['lpid'];
$phone=$myrow['phone'];
?>
<tr><td><?echo $phone;?></td><td>
<a onclick="dellandlordphone(<?echo $lpid;?>,<?echo $llid;?>);">
[x]
</a>
<?
</td></tr>
}//while

Note that we're passing both IDs to the JavaScript handler. The first ID, lpid, uniquely identifies a landlord-phone pair in the database. It is all we need to delete a record. The landlord ID is needed both for calling the display function as well as targeting the container.

In landlords.js, we add the deletion handler:

dellandlordphone=function(lpid,llid){
if (!confirm('Are you sure?')) return;
ajxpgn('landlordphonenumbers_'+llid, document.appsettings.codepage+
'?cmd=dellandlordphone&lpid='+lpid+'&llid='+llid);
}

Add a clause to the switch in myservices.php:

case 'dellandlordphone':
include 'icl/dellandlordphone.inc.php';
dellandlordphone();
break;

Create the server-side handler dellandlordphone.inc.php:

<?php
include 'icl/listlandlordphonenumbers.inc.php';

function dellandlordphone(){
$lpid=GETVAL('lpid');
$llid=GETVAL('llid');
global $db;
$query="delete from landlordphonenumbers where
lpid=$lpid and llid=$llid";
sql_query($query,$db);

listlandlordphonenumbers($llid);
}

Again, lpid is enough to uniquely locate and delete the record. The reason llid is added to the where-clause is because it could catch logic programming errors. It's a good sanity check to have.