This code is a particularly advanced. However the advanced parts are more in that we are setting attributes of more fields and buttons as well as writing our own functions to process and retrieve data.
You will notice that I am setting alot of attributes. This allows me to be sure that the fields look the way we want.
I also check the forms action and display the accessid field differently.
If adding a new record then the form displays with accessid as a selection list
otherwise it displays the accessid as text.
To further implement this I have overridden some of the processing functions.
selectPerson will retrieve the Person data and display the data appropriately.
processPerson will update or delete the person record
While this is a fairly advanced example there is seldom need to go to these lengths to create a form. This example is purely to show the extent to which the code can be used.
demo3.php
<?php
/*
* Intermediate Demonstration
*
* Example of using dbForm to display
* and process a web-form for a database table.
*
* Uses a person table with the following MySQL structure
* CREATE TABLE `person` (
* `personid` int(11) NOT NULL auto_increment,
* `firstname` varchar(50) default NULL,
* `lastname` varchar(50) default NULL,
* `username` varchar(20) default NULL,
* `password` varchar(32) default NULL,
* `notes` text,
* `accessid` int(11) default '1',
* `active` tinyint(1) unsigned default '1',
* `canceldate` date default NULL,
* PRIMARY KEY (`personid`),
* UNIQUE KEY `personid` (`personid`),
* KEY `personid_2` (`personid`)
* ) TYPE=MyISAM COMMENT='person table demo';
*
* and the access table with the following MySQL structure and data
* CREATE TABLE `access` (
* `accessid` tinyint(3) NOT NULL auto_increment,
* `name` varchar(20) default NULL,
* PRIMARY KEY (`accessid`),
* UNIQUE KEY `accessid` (`accessid`),
* KEY `accessid_2` (`accessid`)
* ) TYPE=MyISAM COMMENT='demo access table';
*
* INSERT INTO access VALUES("1", "Admin");
* INSERT INTO access VALUES("2", "User");
*
*/
// include the necessary files
include_once("/path/to/adodb/adodb.inc.php");
include_once("/path/to/dbform/formFields.inc");
include_once("/path/to/dbform/template.inc");
include_once("/path/to/dbform/dbForm.inc");
// create a database connection
$conn = &ADONewConnection('mysql');
$conn->Connect('localhost', 'username', 'password', 'datasource');
$conn->debug = false;
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$dbForm = new dbForm("Person", $conn, "person");
// validation
$dbForm->validateFields = true;
// true parameter forces autoSelection
$dbForm->setSearchField("personid","select personid, lastname, firstname from person where canceldate is null order by lastname, firstname", ", ", "Select a Person", true);
$dbForm->changeFieldType("password","password");
$dbForm->setCheckbox("active",1,0);
// determine what the form action is and set the newRecord flag
$newRecord = false;
$formAction = $dbForm->getFormAction();
if ($formAction=="new") {
$newRecord = true;
}
// set accessid to select only if adding new record
if ($newRecord) {
// new record so make a selection list
$dbForm->changeFieldType("accessid","select");
$dbForm->setFieldTitle("accessid","Access Level:");
} else {
// not a new record
// so disable the accessid field
$dbForm->setFieldAttributes("accessid",array("disabled"=>true));
}
// set field Titles
$dbForm->setFieldTitles(array("firstname"=>"Firstname","lastname"=>"Lastname:","username"=>"Login name:","password"=>"Password:","notes"=>"Notes:","active"=>"Active:","canceldate"=>"Cancel Date:"));
// set field Attributes
$dbForm->setAllFieldsAttributes(array("class"=>"content2","style"=>"width:140px;"));
$dbForm->setFieldAttributes("dbform_searchid",array("style"=>"width:450px"));
$dbForm->setFieldAttributes("notes",array("rows"=>4,"cols"=>40, "style"=>null));
$dbForm->setFieldNotNull("firstname");
$dbForm->setFieldNotNull("lastname");
$dbForm->setFieldNotNull("username");
$dbForm->setFieldNotNull("password");
$dbForm->setFieldAttributes("active",array("style"=>null));
// make the buttons images
$dbForm->changeButtonType("update","image");
$dbForm->setButtonAttributes("update",array("src"=>"update.png"));
$dbForm->changeButtonType("new","image");
$dbForm->setButtonAttributes("new",array("src"=>"new.png"));
$dbForm->changeButtonType("delete","image");
$dbForm->setButtonAttributes("delete",array("src"=>"delete.png"));
$dbForm->changeButtonType("insert","image");
$dbForm->setButtonAttributes("insert",array("src"=>"insert.png"));
$dbForm->changeButtonType("cancel","image");
$dbForm->setButtonAttributes("cancel",array("src"=>"cancel.png"));
$dbForm->setProcessingFunction("select","processPerson");
$dbForm->setProcessingFunction("update","processPerson");
$dbForm->setProcessingFunction("delete","processPerson");
$dbForm->processForm();
if ($newRecord) $dbForm->getSelectValues("accessid","select accessid, name from access");
$dbForm->setTemplateFile("demo3.tpl");
$dbForm->displayForm();
function processPerson($conn, $table, $httpvars, $httpfiles, $searchField, $searchid, $action, $alwaysUpdate) {
// new function to process insert, updates and deletes of person records
// set the returnArray
$returnArray = array();
// determine the action to perform
switch ($action) {
case "select":
// select the record
if ($searchid=="dbform_null") $searchid="null";
$qrysql = "select * from $table where ($searchField=$searchid)";
$rs = $conn->execute($qrysql);
if (!$rs->EOF) {
$results = $rs->fetchRow();
foreach ($results as $field=>$value) {
$returnArray[$field]["value"]=$value;
}
$rs->close();
// see if there is an access to retrieve
if (!empty($results["accessid"])) {
// retrieve the access
$qrysql = "select name from access where accessid=".$results["accessid"];
$rs = $conn->execute($qrysql);
if (!$rs->EOF) {
$access = $rs->fetchRow();
$returnArray["accessid"]["value"]=$access["name"];
$rs->close();
}
}
}
$returnArray["dbform_searchid"]["value"]=$searchid;
break;
case "update":
// updates the current record
// remove the value of accessid
$tempvars = array();
foreach ($httpvars as $field=>$value) {
if ($field=="accessid") continue;
$tempvars[$field]=$value;
}
$httpvars = $tempvars;
// perform the update
// get the current record
$qrysql = "select * from $table where $searchField=".$httpvars[$searchField];
$rs = $conn->execute($qrysql);
// get the update sql
$updateSQL = $conn->getUpdateSQL($rs, $httpvars);
$conn->execute($updateSQL);
$rs->close();
// retrieve the current data
$returnArray = processPerson($conn, $table, $httpvars, $httpfiles, $searchField, $searchid, "select", false);
break;
case "delete":
// delete the record
// instead of deleting we are going to set the canceldate to todays date
// and the select list only displays records which have a null canceldate
$qrysql = "update $table set canceldate=".$conn->DBDate(time())." where $searchField=$searchid";
$conn->execute($qrysql);
break;
}
// return the result
return $returnArray;
} // processPerson()
?>
This template is a bit more advanced than the previous ones but only in that it uses style sheets to layout things out a bit nicer. It also places each field exactly rather than relying on the {:each field} sections used previously.
demo3.tpl
<link href="demo3.css" rel="stylesheet">
<script language="javascript" src="/dbForm.js"></script>
{:form}
<table border="0" width="500" align="center" cellpadding="0" cellspacing="0">
<caption class="sectionTitle">{:FORMNAME}</caption>
{:action="null,select,insert,update,delete,cancel"}
<tr>
<td width="500" colspan="5" class="tableheader">Search:</td>
</tr>
<tr>
<td width="450" colspan="4">{:FIELD="dbform_searchid"}</td>
<td width="50" align="center">
{:action="null,select,insert,update,delete,cancel"}
{:BUTTON="new"}
{:end action}
</td>
</tr>
<tr>
<td width="500" colspan="5" align="right">
<font color="red" size="-2"> {:status}</font>
</td>
</tr>
{:end action}
{:ACTION="new,insert,select,update"}
<tr>
<td width="500" colspan="5" align="center" class="tableheader">Person Details</td>
</tr>
<tr>
<td width="75" class="content1">Firstname:</td>
<td width="150" class="content1">{:field="firstname"}</td>
<td width="75" class="content1">Lastname:</td>
<td width="150" class="content1">{:field="lastname"}</td>
<td WIDTH="50" rowspan="4" align="center" valign="top">
{:ACTION="select,insert,update"}
{:BUTTON="update"}<br>
{:button="delete" confirm="Are you sure you wish to delete this record?"}<br>
{:END ACTION}
{:ACTION="new"}
{:button="insert"}<br>
{:button="cancel"}<br>
{:END ACTION}
</td>
</tr>
<tr>
<td width="75" class="content2">Login name:</td>
<td width="150" class="content2">{:field="username"}</td>
<td width="75" class="content2">Password:</td>
<td width="150" class="content2">{:field="password"}</td>
</tr>
<tr>
<td width="75" class="content1">Access:</td>
<td width="150" class="content1">{:field="accessid"}</td>
<td width="75" class="content1">Active:</td>
<td width="150" class="content1">{:field="active"}</td>
</tr>
<tr>
<td width="75" class="content2">Notes:</td>
<td width="375" class="content2" colspan="3">{:field="notes"}</td>
</tr>
{:end action}
</table>
{:hidden}
</form>
The files that have been used in this demonstration can be downloaded so you can try them out for yourself.