Filter Group Demonstration

As with all filtering, to set up filter groups requires making a call to the setFilterGroup method after creating the selection list.
To see how to setup a filter group lets look at an example.

In our example we have a person form and we want to filter the search list by lastnames.

Firstly we create the search list:
$dbForm->setSearchField("personid","select personid, lastname, firstname from person where canceldate is null and (1=1) order by lastname, firstname", ", ", "Select a Person", true);

Note that we have included (1=1) in the query. This is a replacement tag that will be replaced with a valid where clause during the filter process.

Next we need to call setFilterGroup
$dbForm->setFilterGroup("personid","lastname",array("A..G","H..O","N..Z"),"(1=1)", "Select a Filter Group");

Lets now look at this to see what it is doing
$field = "personid" which tells setFilterGroup to filter the personid field.
$filterField = "lastname" which tells setFilterGroup to use lastname as the field to replace in the search query.
$filterGroup = array("A..G","H..O","N..Z") which tells setFilterGroup to use 3 filter groups consiting of A to G, H to O and N to Z.
$filterReplace = "(1=1)" which tells setFilterGroup to replace (1=1) in the search query with a where clause on the $filterField field (i.e. lastname)
$defaultText = "Select a Filter Group" which tells setFilterGroup to display this text at the start of the list.

When run this example will display a filter list consisting of the default text and the 3 specified groups, then once a group is selected the SearchField query will be run with the filterReplace tag replaced with a where clause and a reset filter item is added to the end of the list.
i.e. if group H..O is select then the Search Field query will look as follows
select personid, lastname, firstname from person where canceldate is null and (lastname between 'H' and 'Oz') order by lastname, firstname

Note: O is changed automatically to Oz to ensure that the O group is acutally found

Be warned that if there are records that do not fall into these groups then they will not be able to be found with this filter method. This is the main limitation of Filter Groups

demo4.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 and (1=1) order by lastname, firstname", ", ", "Select a Person", true);

// set Filter Grouping
$dbForm->setFilterGroup("personid","lastname",array("A..G","H..O","N..Z"),"(1=1)","Select a Filter Group");

$dbForm->changeFieldType("password","password");
$dbForm->setCheckbox("active",1,0);
$dbForm->changeFieldType("accessid","select");

// set field Titles
$dbForm->setFieldTitles(array("firstname"=>"Firstname","lastname"=>"Lastname:","username"=>"Login name:",
    "password"=>"Password:","notes"=>"Notes:","active"=>"Active:","canceldate"=>"Cancel Date:",
    "accessid"=>"Access Level:"));

// set field Validation
$dbForm->setFieldNotNull("firstname");
$dbForm->setFieldNotNull("lastname");
$dbForm->setFieldNotNull("username");
$dbForm->setFieldNotNull("password");

// make the buttons images
$dbForm->changeButtonType("update","image");
$dbForm->changeButtonType("new","image");
$dbForm->changeButtonType("delete","image");
$dbForm->changeButtonType("insert","image");
$dbForm->changeButtonType("cancel","image");

$dbForm->processForm();

$dbForm->getSelectValues("accessid","select accessid, name from access");

$dbForm->setTemplateFile("demo4.tpl");

$dbForm->displayForm();

?>

Template File

The template for this demonstration is fairly straight forward however it does use a new feature introduced in dbForm 0.8.2. It has the ability to incorporate attributes in the dbForm tags.

For example if we want a field to be displayed with a class and a specific style we can now incorporate that into the template rather than having to specify it in code. This makes the templates as flexible as possible.

In this example we are adding a class and style to a named field tag
{:field="firstname" class="content2" style="width:140px;"}

This will be replaced in the output html as something like:
<input type="text" name="firstname" value="Tony" class="content2" style="width:140px;" />

This allows almost all formatting to be controlled via the template if required.

demo4.tpl

<link href="demo4.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="dbform_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" class="content2" style="width:450px;"}</td>
      <td width="50" align="center">
{:action="dbform_null,select,insert,update,delete,cancel"}
         {:BUTTON="new" src="new.png"}
{:end action}
      </td>
   </tr>
   <tr>
      <td width="500" colspan="5" align="right">
         <font color="red" size="-2">&nbsp;{: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" class="content2" style="width:140px;"}</td>
      <td width="75" class="content1">Lastname:</td>
      <td width="150" class="content1">{:field="lastname" class="content2" style="width:140px;"}</td>
      <td WIDTH="50" rowspan="4" align="center" valign="top">
{:ACTION="select,insert,update"}
         {:BUTTON="update" src="update.png"}<br>
         {:button="delete" src="delete.png"}<br>
{:END ACTION}
{:ACTION="new"}
         {:button="insert" src="insert.png"}<br>
         {:button="cancel" src="cancel.png"}<br>
{:END ACTION}
      </td>
   </tr>
   <tr>
      <td width="75" class="content2">Login name:</td>
      <td width="150" class="content2">{:field="username" class="content2" style="width:140px;"}</td>
      <td width="75" class="content2">Password:</td>
      <td width="150" class="content2">{:field="password" class="content2" style="width:140px;"}</td>
   </tr>
   <tr>
      <td width="75" class="content1">Access:</td>
      <td width="150" class="content1">{:field="accessid" class="content2" style="width:140px;"}</td>
      <td width="75" class="content1">Active:</td>
      <td width="150" class="content1">{:field="active" class="content2"}</td>
   </tr>
   <tr>
      <td width="75" class="content2">Notes:</td>
      <td width="375" class="content2" colspan="3">{:field="notes" class="content2" rows="4" cols="40"}</td>
   </tr>
{:end action}
</table>
{:hidden}
</form>

Download Demo files

The files that have been used in this demonstration can be downloaded so you can try them out for yourself.

demo4.zip