Database-Driven Applications
The key to building a compelling application that can be accessed from both a WAP-enabled phone and an HTML browser is synergy. Use the strengths of each environment to enhance the user experience, and tailor what you will deliver to the device. The beauty of the Web is its ability to deliver dynamic data that is constantly changing and unique to each user as appropriate. To this end, the information that you are going to present should live in a database. There are many free, and not free, database choices out there on every platform with drivers for any language that you want to use. For the sake of simplicity (and space), I have chosen one database and one language to present code in, but the underlying principles can be applied to any database or language.
Introduction to Employee Directory
Listing 13.1 is an example of an employee phone list that can be accessed from either an HTML browser or a WAP browser from the same URL. Although in reality there are really two different applications here, they both provide an interface into the same data store, and updates in one medium will propagate to the other. The phone book application is based on a simple SQL database running in mSQL (from Hughes Technologies at http://www.hughes.com.au/). Database access is accomplished through the w3-msql library that ships with mSQL. Although the code used in this chapter is specific to mSQL, the queries and principles used can be applied to any SQL database. Of course, similar code could be written in ASP, ColdFusion, Perl, JSP, and many other languages (as will be seen in the final chapters of this book).
The database that this application uses relies on one table named emp_details. The table has six columns: emp_no, first_name, last_name, phone, email, and dept. The emp_no is a sequential number that is unique and generated by the database for every row that is added to the table. The others are simply character values.
The application is accessed by the same URL from the PC browser or from the WAP browser. This is done by using a script that examines the value of the HTTP_ACCEPT string and then re-directs the client to the appropriate code.
Listing 13.1 An Employee Phone List
HTML Entry Point
The entry point into this application is going to look slightly different between an HTML browser and a WAP browser for several reasons. In an HTML browser, it is safe to assume enough real estate and memory to simply display the entire phone list in a table when the user requests it. This is a very simple page, which could certainly be enhanced with a corporate banner at the top of the window and some navigation links along the left edge of the window. However, these should be kept small and relatively inconspicuous, since the employee information is what users are really interested in. This is shown in Figure 13.6.
Figure 13.6. We can display all rows of the phone tree in the table since neither screen size nor maximum page size are constraints on the HTML browser.
The page in Figure 13.6 is generated by a simple query into the database that looks like this:
if (msqlQuery($sock,"SELECT first_name, last_name, phone, email, emp_no, dept FROMemp_details ORDER BY last_name") < 0) { echo("Error : $ERRMSG "); exit(1); } $res = msqlStoreResult(); $row = msqlFetchRow($res); > <table border> <tr> <th>First name</th><th>Last Name</th> <th>Phone</th><th>Email</th><th>Department</th> </tr> <! while (#$row > 0) { printf("<form action=edit_emp.msql method=POST> <tr><td>%s</td><td>%s</td><td>%s<
If we temporarily ignore everything except for the SQL statement, we can see that the application simply asks for every field from each row of the database. Stepping out a bit further we can see that there is a level of error checking built in to the application so that we can return an error from the query if one is generated (rather than causing the browser to hang or return an effectively meaningless error type 500). The results of the query are stored in a local variable $res, and then one row at a time is pulled out of the results and stored in an array, $row.
The HTML code that is used to wrap the results of the SQL query presents each row of data in a table. The entire result is iterated over, and the elements from the array are extracted and presented in a table cell. Each email address is wrapped in an <a href="mailto:"> link, which will allow users to invoke their email client directly from their PC browser. The last column of the table contains an Edit button that will allow the user to update the record in the database (this will be discussed later on in this chapter).
Phone Entry Point
Due to screen display limitations as well as the limit on the amount of information that can be delivered to a WAP phone at one time, the entry point of the application for the WAP phone is going to be simplified to just present the list of employee names. Figure 13.7 shows a screenful of the WAP presentation.
Figure 13.7. Even if the entire database were small enough to fit in a single deck, the table cannot be displayed in a meaningful way, so each name in the list becomes a link of its own.
In addition to the fact that only the names from the database are initially displayed because of memory limitations in WAP devices, we have to limit the number of records we can present to the user at one time. To allow access to the entire employee database, we need to keep track of which names have been delivered. Since we are only going to present nine possible records from the database in any given card, we can make the tenth item a More... link, which will present another screenful of data. To do this, we need a slightly more sophisticated select statement for our SQL select:
if (msqlQuery($sock,"SELECT first_name, last_name, phone, email, emp_no, dept FROM
Notice that in the SQL select statement we include a WHERE clause so that we can limit exactly which records are going to be selected based on a value of last_name. When this select statement is first called, there is no value for last_name, so the query will retrieve records from the start of the alphabet. The entire result of the query is stored locally in a variable, $res, and then read out one row at a time. A counter is used to keep track of how many rows have been read, and when the ninth record is reached, a More... link is built, with a destination of the script and the last name set as the value of the last name displayed. When a user then requests this last link, the script is re-invoked, this time with the last_name value assigned, ensuring that the correct record set is retrieved.
<option onpick="empList.wsql?last_name=Longsreth">more...</option></select>
The application relies on this last name value, rather than the unique identifier of emp_no because the value of emp_no is not going to be sequential according to the last_name. After either the More... link is built or the end of the data has been reached, the card is closed off, and the script re-iterates back over the results, building the individual cards for each record that will display the data.
Once a user selects one of the names in the list, the details associated with that user will be displayed on a card of its own. This is the only way to represent the data in a user-friendly and meaningful way. When the user selects one of the names, their details will be displayed in full on a new card. Figure 13.8 shows the details for one of the employees on the list.
Figure 13.8. When the user selects one of the employee names, all of their details are displayed. The phone number is surrounded with a wtai://wp/mc; so that the number is callable directly from the browser.
Since we know that the user is going to be using this application from the phone, and it is a phone book, it only makes sense to allow the user to be able to generate a call from the results. The UP.Browser from Phone.com will allow the user to select the link that displays the phone number, and automatically dial the number. Other devices may just present the user with a Use Number feature that will drop the phone number into the phone's voice interface, and the user can then choose to make the call. In the future, as more of the WTAI (Wireless Telephony Application Interface) specification from the WAP forum is implemented by devices, it will be possible to present the user with an option to add the information to the local phone book on his device, or maybe send a text message to the user.
Remember that in the HTML application, the email address is presented as a hyperlink with a mailto: action that will allow the user to launch his email application directly from his browser. Although there are WML-based email programs, there is no standard way to invoke them at this time, so the email address in the WML application is not presented as an active link.
HTML-Specific Features
The HTML application has some additional features beyond simply displaying the phone information. It allows users to either edit an existing listing or add a new listing to the database. These functions could be presented in the WML version of the application, but the entry of data from the phone is often an arduous process and should be avoided whenever possible. Providing an HTML interface into the data gives the user a much more usable way to manage the information.
Adding an Employee to the Database
An employee may be added via the Add button at the bottom of the table, and brings up a simple HTML form page as shown in Figure 13.9.
The action on this form is to post all of the field values to a script, which will then create a new row in the emp_details table in our database, generate an employee number for the record, and insert the data.
if (msqlQuery($sock, "select _seq from emp_details") < 0) {
fatal("Query failed : $ERRMSG");
}
$res = msqlStoreResult();
$row = msqlFetchRow($res);
$sequence = (int)$row[0];
msqlFreeResult($res);
/*
** Insert the record
*/
$q = "insert into emp_details values ($sequence, '$first_name',
'$last_name', '$dept', '$phone', '$email')";
if (msqlQuery($sock, $q) < 0) {
fatal("Add employee failed : $ERRMSG");
}
msqlClose($sock);
Figure 13.9. This HTML form allows a user to input the data. The action taken when the user presses the Add Employee button will add a new record into the database.
The SELECT _seq statement will generate the new sequential number that can then be used as the emp_no value. This value, along with all of the data that was picked up in the POST, is inserted into a new row in the database. When the update is completed, the user is returned to the employee list with the new record displayed in the appropriate alphabetical order.
Editing an Existing Record
The other HTML-specific feature for the database is the ability to edit an existing record. The Edit button that is presented in the last column of each row will build an edit card for the employee listed in that row, as you can see in Figure 13.10.
Figure 13.10. This HTML form allows a user to edit the data associated with a given employee. The fields in the form are automatically filled in so the user only needs to change those that apply. Changes are committed to the database as soon as the user presses the Edit button.
The edit sequence relies on the emp_no field in the database, which is stored in a hidden field in the form. This ensures that only the correct record is going to be updated.
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN"> <html> <HEAD><TITLE>Edit Employee Record</TITLE></HEAD> <BODY> <CENTER> <H2>Edit Employee Record</H2> <FORM ACTION=update_emp.msql METHOD=POST> <TABLE align=center> <TR> <form action=update_emp.msql <table> <tr><td>First Name</td><td><input name=first_name value=Vladimir></td> <td>Last Name</td><td><input name=last_name value=Bordeaux></td></tr> <tr><td>Phone</td><td><input name=phone value=650-555-1582></td> <td>Email</td><td><input name=email [email protected]></td></tr> <td>First Name</td><td><select name=dept value=sales> <OPTION VALUE=sales>Sales <OPTION VALUE=dev>Development <OPTION VALUE=admin>Administration <OPTION VALUE=cs>Customer Support <input type=hidden name=emp_no value=113> </table> <input type="submit" name="Edit" value="Edit"> </form> </body> </html>
The script that receives the post behaves very much like the script that adds a new employee to the database (this edit form should have looked familiar), however instead of performing an INSERT, it will UPDATE the record that is associated with the emp_no.
<!
$emp_no = (int) $emp_no;
if (msqlQuery($sock,"UPDATE emp_details
SET first_name = '$first_name', last_name = '$last_name',
phone = '$phone', email = '$email', dept = '$dept'
WHERE emp_no = $emp_no") < 0)
{
echo("Error : $ERRMSG
");
exit(1);
}
msqlClose($sock);
The int in the very first line of this code, the $emp_no value that came in with the postdata, is cast to an int so it can be effectively used in the SQL query. It must be cast this way, otherwise it will be represented as a string, and there will be a type mismatch result from the SQL statement. The UPDATE is otherwise very simple and straightforward.
Again, there is no technical limitation to prevent these add and edit functions from being presented on the phone. It is just not done here for space reasons, and also to underscore the point that data entry is simpler and cleaner from an HTML interface.
Phone-Specific Functionality
Since the phone cannot display all of the records at the same time on the initial screen, it is important to include a search capability for the database. This prevents users from having to scroll through many screens of results before finding the employee they are looking for. This feature is not really needed for the HTML application since all records are displayed on the same page, and users can use the Control-F find function from their HTML browser to search for an employee.
If you look back to Figure 13.7, you will notice the Find label bound to the options soft key. This action will bring up a find deck so the user can choose to search for a employee based on first name or last name. The searches are exclusive, meaning that they cannot search for first and last name at the same time.
Figure 13.11. The WML application needs to allow the user to search for an employee based on first or last name. The user selects what he wants to search by, and a SQL query is generated.
The first or last name that is entered is then delivered on the query string to a script that queries the database for matching records. If a match is found, only the first match is returned, and if there is no match, a No Match card is returned instead. In addition to the matching record (or no results), an "all" action is bound to the options key. This will present the user with the employee listing, starting at the top of the alphabet. Following is the code for the script that runs the query and returns the results:
if(#$first_name >0){
if (msqlQuery($sock,"SELECT first_name, last_name, phone, email, emp_no FROM emp_details
WHERE first_name LIKE '%$first_name%'") < 0)
{
echo("Error : $ERRMSG
");
exit(1);
} }
else {
if (msqlQuery($sock,"SELECT first_name, last_name, phone, email, emp_no FROM emp_details
WHERE last_name LIKE '%$last_name%'") < 0)
{
echo("Error : $ERRMSG
");
exit(1);
} }
$res = msqlStoreResult();
$row = msqlFetchRow($res);
if (#$row > 0){
printf("%s %s<br/><a href="wtai://wp/mc;%s" title="call">%s</a><br/>%s<br/>",
$row[0], $row[1], $row[2],$row[2], urlEncode($row[3]));
}
else{
echo("$first_name $last_name Not Found!");}
echo("</p>
</card>
</wml>");
The script relies on the definition of the first_name variable from the HTTP request to decide if the SQL query should match first or last name. This code could be enhanced to return multiple matches if they exist, but for simplicity's sake, it will only return one row of data.
Application Conclusions
The Employee Database application is just one simple possibility that can be used to present the same data to vastly different clients. The code that is used to perform this task is similar across the HTML and WML versions, but the key considerations of how much data to display at once, how to display it in a meaningful way, and how to take advantage of client-specific features are taken to heart.