Server-side LiveCode: Forms & Database Access

DigHT 310
Server-side LiveCode scripting
Forms and Database Assignment

For this assignment you will draw on what we have learned regarding HTML forms, GET and POST, and databases to create an on-line report creator based on your own database. Here are the parameters for this assignment:

The first irev/html document will present the user a simple form, which will allow them to choose which database table to base the report on. This form will use the GET method to call up a second page.
The second document will display the type of report the user chose, and display a form with checkboxes that will let them choose what table columns will appear in the report. This form will vary depending on which table the user chose as the source of the report. This form will use the POST method and call up a third page that will show the report results.
The third page will use LiveCode database calls to make the database queries, then format the results into a neat HTML presentation. This could use tables or lists, for example.

(See this link to a partially completed example of the type of online report you will be creating in this assignment.)

Step-by-step guide

Find the file called template.irev in the Templates folder on the DigHT 310 file server. Make a copy of it and save it someplace easy to get to. This template will become the framework of your three irev files. If you can't find this template file, it's just a simple html document template with a few common header tags. Add your scripting between the comments. The template looks like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
   <meta http-equiv="content-type" content="text/html; charset=utf-8" />
   <title>Untitled</title>
   <link type="text/css" rel="stylesheet" href="http://livecode.byu.edu/baseStyles.css" />
   
</head>
<body>
<!-- Page content goes here: -->


<!-- End of page content -->
</body>
</html>

In your first page create a form for choosing which table to base the report on. Use the GET method for submitting the form.

<p>
Select the Report Area:
</p>
<form action="configreport.irev" method="get" name="choosetable">
<p>
   <select name="choice">
   <option label="People" value="people">People</option>
   <option label="Departments" value="department">Departments</option>
   </select>
</p>
<p>
   <input name="Go" type="submit" />
</p>

Create a second irev document using the template. Make sure the name of your second document matches the action="" attribute from the form on the first page. It should also be saved in the same directory on the server.
This page should give the user checkboxes to choose what they want to include in the report. The checkboxes will vary depending upon what was selected on the first page.

Remember that a form using the GET method creates argument lists in this form:
```
  ?name1=value1&name2=value2, etc.
```
When we worked with web services in LiveCode stacks we had to break these arg lists down ourselves. RevServer does this for us automatically. It puts the arguments into a special array variable called $_GET. So for the arg list above we would have:
```
  $_GET["name1"] --> contents is "value1"
  $_GET["name2"] --> contents is "value2"
```

So by checking the $_GET array in our page two document, and using a simple if-then-else statement, we can create a customized form for each choice from page 1.

<?rev
set the errormode to "inline" -- this is useful for debugging
include "scripts.irev"
put $_GET["choice"] into tWhich
?>
<p>Configure your <?rev put tWhich ?> report:</p>
<div>
<form method="post" action="displayreport.irev">
<p>Include the following information:</p>
<?rev
if tWhich is "people" then
   # Example of building html with livecode scripting
   put "<p><input name=" & q("dept") & " type=" & q("checkbox") & " value=" & q("yes") & " />Department </p>" & return
   put "<p><input name=" & q("phone") & " type=" & q("checkbox") & " value=" & q("yes") & " />Phone Number </p>" & return
   put "<p><input name=" & q("office") & " type=" & q("checkbox") & " value=" & q("yes") & " />Office </p>" & return
else if tWhich is "department" then
?>
	<!-- Example of mixing LiveCode blocks with HTML blocks -->
   <p><input name="supervisor" type="checkbox" value="yes" />Supervisor </p>
   <p><input name="phone" type="checkbox" value="yes" />Dept. Phone Number
   <p><input name="office" type="checkbox" value="yes" />Office </p>
<?rev 
end if
?>
<input name="choice" type="hidden" value="<?rev put tWhich ?>" />
<p><input name="submit" type="submit" value="Create Report" />
</form>
</div>

In this form we're using the POST method and a third irev page to process the action. (There is no particular reason for using POST here instead of GET; it's just to show that we can use either in irev documents.)

Create a third irev document using the template. Make sure the name of this document matches the action="" attribute from the form on the second page. Save it in the same directory on the server as the other two pages.

This page will query the database using the criteria determined on the second page and display the results. Here is the code for doing that, but without formatting the database query results:

<?rev
set the errormode to "inline"
put "<p>Something here?</p>" 

function q pString
   return quote & pString & quote
end q

put $_POST["choice"] into tWhich
if tWhich is "people" then
   put $_POST["dept"] into tIncludeDept
   put $_POST["phone"] into tIncludePhone
   put $_POST["office"] into tIncludeOffice
   put "SELECT fname,lname" into tQuery
   if tIncludeDept <> empty then 
     put comma & "dept" after tQuery
   end if
   if tIncludePhone <> empty then
     put comma & "phone" after tQuery
   end if
   if tIncludeOffice <> empty then
     put comma & "office" after tQuery
   end if
else if tWhich is "department" then
   put $_POST["supervisor"] into tIncludeSuper
   put $_POST["phone"] into tIncludePhone
   put $_POST["office"] into tIncludeOffice
   put "SELECT name" into tQuery
   if tIncludeSuper <> empty then
     put comma & "supervisor" after tQuery
   end if
   if tIncludePhone <> empty then
     put comma & "phone" after tQuery
   end if
   if tIncludeOffice <> empty then
     put comma & "office" after tQuery
   end if
end if
put " FROM " & tWhich after tQuery

# create database connection
put revOpenDatabase("mysql","hummac.byu.edu:33600","acmeco",\
   "chum310","livec0de") into tConnID
put revDataFromQuery(,,tConnID,tQuery) into tReport
revCloseDatabase tConnID

put "<p>"
put tReport -- debugging
put "</p>"
?>

The script above confirms that we're getting valid results. Now we have to format the results into a readable form. An HTML table works nicely for this purpose. Remember that HTML tables use the following tag set:

 <table cellpadding="20">
    <tr>
      <th>Header 1</th><th>Header 2</th>
   </tr>
   <tr>
      <td>Row 1 cell 1</td><td>Row 1 cell 2</td>
   </tr>
   <tr>
      <td>Row 2 cell 1</td><td>Row 2 cell 2</td>
   </tr>
</table>

You can add border='n' and cellpadding='n' attributes to the table tag to create borders around the cells and empty space around the cell contents if you wish, like this:

<table border='1' cellpadding='4'>

Here's one formatting solution. You will note that lines are added to the if-then statements to create the proper table headers. The last block of code creates the necessary table elements to frame the query results neatly.

<?rev
set the errormode to "inline"

function q pString
   return quote & pString & quote
end q

put $_POST["choice"] into tWhich
if tWhich is "people" then
   put $_POST["dept"] into tIncludeDept
   put $_POST["phone"] into tIncludePhone
   put $_POST["office"] into tIncludeOffice
   put "SELECT fname,lname" into tQuery
   put "<th>First name</th><th>Last name</th>" into tHeader 
   if tIncludeDept <> empty then 
     put comma & "dept" after tQuery
     put "<th>Department</th>" after tHeader  
   end if
   if tIncludePhone <> empty then
     put comma & "phone" after tQuery
     put "<th>Phone</th>" after tHeader
   end if
   if tIncludeOffice <> empty then
     put comma & "office" after tQuery
     put "<th>Office</th>" after tHeader
   end if
else if tWhich is "department" then
   put $_POST["supervisor"] into tIncludeSuper
   put $_POST["phone"] into tIncludePhone
   put $_POST["office"] into tIncludeOffice
   put "SELECT name" into tQuery
   put "<th>Dept name</th>" into tHeader 
   if tIncludeSuper <> empty then
     put comma & "supervisor" after tQuery
     put "<th>Supervisor</th>" after tHeader
   end if
   if tIncludePhone <> empty then
     put comma & "phone" after tQuery
     put "<th>Phone</th>" after tHeader
   end if
   if tIncludeOffice <> empty then
     put comma & "office" after tQuery
     put "<th>Office</th>" after tHeader
   end if
end if
put " FROM " & tWhich after tQuery

# create database connection
put tQuery & "<br />" --debugging
put revOpenDatabase("mysql","hummac.byu.edu:33600","acmeco",\
   "chum310","livec0de") into tConnID
put revDataFromQuery(,,tConnID,tQuery) into tReport
revCloseDatabase tConnID

# create the table elements for displaying the results
put "<table border='1' cellpadding='4'>" & return
put "<tr>" & tHeader & "</tr>" & return
set the itemDelimiter to tab
repeat for each line tLine in tReport
   replace tab with "</td><td>" in tLine
   put "<tr><td>" & tLine & "</td></tr>" & return after tFormattedRept
end repeat
put tFormattedRept
put "</table>"
?>

Test your forms using various choices to change the final report contents. Make sure each variation is working correctly. You may also want to add explanatory text to the report page so that the user knows what the report is for.
Here is a link to a partially completed example of the online report described here. You'll notice that we haven't substituted Department names for department id in the people report, nor supervisor names for person ids in the department report. Based on how we did this in the stack-based version of our report, figure out a way to do it in your own web-based report.

This asssignment is due next class period.

Back BYU LiveCode Lessons Gateway

DigHT 310 Server-side LiveCode scripting Forms and Database Assignment

Step-by-step guide

This asssignment is due next class period.

DigHT 310
Server-side LiveCode scripting
Forms and Database Assignment