PHPRO.ORG

This class has several features which allow the generation of forms from a database using PDO. The use of PDO gives greater flexibility in the choice of database as many different databases are supported. For more information on this and PDO in general it is recommended to read the article Introduction To PDO. Before we can begin, we need a simple database. For the purpose of this tutorial we will be using MySQL as the database. Here we have the database definition we will be using. Create a database called cart and then use this to create tables. If you do not know how to use a database dump, then you should read the Introduction to PHP and MySQL page.

CREATE TABLE cart_users (
cart_user_id int(9) NOT NULL auto_increment,
cart_user_name varchar(20) NOT NULL,
cart_user_username varchar(20) NOT NULL,
cart_user_password varchar(20) NOT NULL,
PRIMARY KEY (`cart_user_id`)
);

CREATE TABLE categories (
cid int(9) NOT NULL auto_increment,
category_name varchar(50) NOT NULL,
category_description varchar(254) NOT NULL,
PRIMARY KEY (cid)
);

CREATE TABLE items (
item_id int(9) NOT NULL auto_increment,
category_id int(9) NOT NULL,
item_price int(9) NOT NULL,
item_description mediumtext NOT NULL,
PRIMARY KEY (item_id)
);

INSERT INTO categories VALUES (1,'cars',''),(2,'cars',''),(3,'trucks',''),(4,'motor cycles',''),(5,'aircraft',''),(6,'boats',''),(7,'trains','');

With the database created we can now put the form generation class to work. To start with just a simple output of the form fields, based on the field names from the items table. You may wish to use this method for generating a form to INSERT data.

<?php
try    {

    
/*** include the class definition ***/
    
include 'PDOFormGen.class.php';

    
/*** a new pdoformgen instance ***/
    
$form = new pdoformgen;

    
/*** set the dsn for the database ***/
    
$form->setDSN("mysql:host=localhost;dbname=cart");

    
/*** set the db username ***/
    
$form->setDbUsername('username');

    
/*** set the db password ***/
    
$form->setDbPassword('password');

    
/*** set the database table name ***/
    
$form->setTableName('items');

    
/*** set the form action ***/
    
$form->setFormAction('process.php');

    
/*** set the value of the submit button ***/
    
$form->setSubmitButtonValue('Add Item');

    
/*** generate the form ***/
    
$html $form->generateForm();

    
/*** display the form ***/
    
echo $html;
    }
catch(
Exception $e)
    {
    echo 
$e->getMessage();
    }
?>

The above script will produce a HTML form such as the one below

<form action="process.php" method="post">
<label for="item_id">Item Id</label>
<input type="text" name="item_id" maxlength="9" value="" id="item_id" />
<label for="category_id">Category Id</label>
<input type="text" name="category_id" maxlength="9" value="" id="category_id" />
<label for="item_price">Item Price</label>
<input type="text" name="item_price" maxlength="9" value="" id="item_price" />
<label for="item_description">Item Description</label>
<textarea cols="40" rows="10" name="item_description" id="item_description" /></textarea>
<input type="submit" value="Add Item" />
</form>

Before we get on to the form itself, lets walk through the class methods we have used to create the form.
The first thing to note is the use of try{} catch(){} block to hold our code. This is because PDO will throw an exception if an error is found. You may deal with the exception any way you wish from the catch block by showing an error of re-throwing the exception. Form more information on exceptions check out the PHPRO tutorial on PHP Exceptions.

Getting into the code itself we first instantiate a new object using $form = new pdoformgen;.
From there we set the DSN (Data Source Name) for the database. In this case we are using mysql, localhost and the database name is, as we saw earlier, cart.
The next two methods simply set the username and password for the database. SQLite users may safely omit these two methods.

Finally, we get into the business of generating the form by setting the database table we wish to generate the form from using the setTableName() method. In the example above we will be generating the form based on the items table, so here it is set to items. Simple.

The form action as defined in the line:
<form action="process.php" method="post">
This value is set using the setFormAction() method. Note here that the method is set to "post". The option to make this variable to either POST or GET was considered, but decided there is too much nastiness with the use of GET in forms.

The value on the submit button can be set also using the setSubmitButtonValue() method. If this method is not called the default value of Submit is used. A form reset button is also available by calling the resetButton() method.

From there it is a simple matter of putting it all together with the generateForm() method, which takes all the values and generates the HTML which can then be displayed using echo or print.

You can use your CSS skills to format this however you will. The maxlength has been set as the value from the database field length. The resulting form looks quite good as it is valid HTML. Note also that the maxlength fields have been filled in to match the lengths of the default database field lengths. The labels have been been formatted from the database names with underscores to like item_id to Item Id. This is taken care of within the class and works also for camelCase type field names which would become Camel Case.

All the fields in the form are <input type="text" ... /> except for the item description form field which is a >textarea<. This is because the database type of the item_description field is MEDIUM_TEXT. In MySQL all text fields are of type "BLOB" and so all TEXT and BLOB fields currently use a textarea field type in the form. Hopefully soon this can be fixed so that there is true differentiation between BLOB and TEXT. The textarea field itself has a default of cols="40" rows="10". This is provided to maintain HTML 4.0 STRICT compliance. The size of the textarea box can be over-ridden with CSS.

Excluding Fields

With the form completed we can further customize the form. The first field is the item_id field. This field is an auto incremented field in MySQL and the field should be left blank so that there is not collisions of id values within the table. With the form field displayed there is a risk of a user filling in the field and entering an incorrect value. To combat this sort of problem, fields can be excluded from the form using the setExcludeFields() method. This method takes an array as its single arguement and is used as follows

<?php

$form
->setExcludeFields(array('item_id'));

?>

Of course, as it takes an array as its arguement, multiple fieldnames may be excluded in a single call as follows. The call to setExcludeFields() would exclude the item_id and the item_description fields.

<?php

$form
->setExcludeFields(array('item_id''item_description'));

?>

Drop Down Box

In the database definition we see that the items table contains a category_id field. This id matches the ID from the categories table. So to properly input a category you either need to know the id number of each category (there may be hundreds in a real cart), or, we can generate a dropdown menu box from the categories table. To do this we use the setCustomField() method. This method takes four, or optionally, five values. They are:

  • $form_fieldname - The field we wish populate
  • $fieldtype - The type of field we wish (dropdown)
  • $db_table - The table we wish to get values from (categories)
  • $db_field - The field name in the database table we wish to use (category_name)
  • $db_id - [optional] The database field name we wish to use as the id (category_id)
  • Lets use what we have so far to generate a new form.

    <?php
    try     {

        
    /*** include the class definition ***/
        
    include 'PDOFormGen.class.php';

        
    /*** a new pdoformgen instance ***/
        
    $form = new pdoformgen;

        
    /*** set the dsn for the database ***/
        
    $form->setDSN("mysql:host=localhost;dbname=cart");

        
    /*** set the db username ***/
        
    $form->setDbUsername('username');

        
    /*** set the db password ***/
        
    $form->setDbPassword('password');

        
    /*** set the database table name ***/
        
    $form->setTableName('items');

        
    /*** set the form action ***/
        
    $form->setFormAction('process.php');

        
    /*** set the value of the submit button ***/
        
    $form->setSubmitButtonValue('Add Item');

        
    /*** exclude the itme_id and item_description fields ***/
        
    $form->setExcludeFields(array('item_id''item_description'));

        
    /*** a dropdown field, $fieldname, $fieldtype, $dbtable, $dbfield $db_id***/
        
    $form->setCustomField('category_id''dropdown''categories''category_name''cid');

        
    /*** generate the form ***/
        
    $html $form->generateForm();

        
    /*** display the form ***/
        
    echo $html;
        }
    catch(
    Exception $e)
        {
        echo 
    $e->getMessage();
        }
    ?>

    Now we see the methods to customize the form in action. The same form action and database values remain, but we have excluded two fields and created a custom field of type "dropdown". From the script above, the form now looks like this:

    <form action="process.php" method="post">
    <label for="item_name">Item Name</label>
    <input type="text" name="item_name" maxlength="50" value="" id="item_name" />
    <label for="category_id">Category Id</label>
    <select name="category_id">
    <option value="1">cars</option>
    <option value="2">cars</option>
    <option value="3">trucks</option>
    <option value="4">motor cycles</option>
    <option value="5">aircraft</option>
    <option value="6">boats</option>
    <option value="7">trains</option>
    </select>
    <label for="item_price">Item Price</label>
    <input type="text" name="item_price" maxlength="9" value="" id="item_price" />
    <input type="submit" value="Add Item" />
    </form>

    The dropdown menu field has been populated with values from the categories table, and the name for the select is set to category_id. The option values are set to the category_id from the categories table. If we had not specified the id from the categories table like this:

    <?php
        
    /*** a dropdown field, $fieldname, $fieldtype, $dbtable, $dbfield ***/
        
    $form->setCustomField('category_id''dropdown''categories''category_name''cid');
    ?>

    Now the option value for the categories looks like this:

    <select name="category_id">
    <option value="aircraft">aircraft</option>
    <option value="boats">boats</option>
    <option value="cars">cars</option>
    <option value="cars">cars</option>
    <option value="motor cycles">motor cycles</option>
    <option value="trains">trains</option>
    <option value="trucks">trucks</option>
    </select>

    The select option values are now set to the values of the field name. Options are available to generate an array of radio buttons or other options and are listed here

    • dropdown
    • radio
    • listbox
    • checkbox
    • password
    • hidden

    Fields With Values

    Up to this point the form class has created an empty for ready to INSERT data. The form values can be filled in with the information from the selected table by simply specifying the condition of the SELECT. Using the categories table for an example, we can generate a form with the values by setting the query condition with the setQueryCond() method as seen in the example below.

    <?php
    try {

        
    /*** include the class definition ***/
        
    include 'PDOFormGen.class.php';

        
    /*** a new pdoformgen instance ***/
        
    $form = new pdoformgen;

        
    /*** set the dsn for the database ***/
        
    $form->setDSN("mysql:host=localhost;dbname=cart");

        
    /*** set the db username ***/
        
    $form->setDbUsername('username');

        
    /*** set the db password ***/
        
    $form->setDbPassword('password');

        
    /*** set the database table name ***/
        
    $form->setTableName('categories');

        
    /*** hide the category id field ***/
        
    $form->setCustomField('cid''hidden');

        
    /*** set the form action ***/
        
    $form->setFormAction('process.php');

        
    /*** set the query condition ***/
        
    $form->setQueryCond('cid = 4');

        
    /*** set the value of the submit button ***/
        
    $form->setSubmitButtonValue('Add Item');

        
    /*** a reset button ***/
        
    $form->setResetButton();

        
    /*** generate the form ***/
        
    $html $form->generateForm();

        
    /*** display the form ***/
        
    echo $html;
        }
    catch(
    Exception $e)
        {
        echo 
    $e->getMessage();
        }

    ?>

    As with previous scripts, an instance is created and the database connection is made. The table to be SELECTed from is set to categories and a custom field is set to hidden for the categories ID (cid) field. The setQueryCond() method is used to set the WHERE condition for the SELECT. We are looking for the record in cateories WHERE cid = 4. When the database finds the record the values are put into the form fields values. Of course any valid SQL condition could be used to generate a row of data.
    Note: A hidden form field has no label tag.

    The resulting form from the code above will look like this:

    <form action="process.php" method="post">
    <input type="hidden" name="cid" value="4" id="cid" />
    <label for="category_name">Category Name</label>
    <input type="text" name="category_name" maxlength="50" value="motor cycles" id="category_name" />
    <label for="category_description">Category Description</label>
    <input type="text" name="category_description" maxlength="254" value="Hell on wheels" id="category_description" />
    <input type="submit" value="Add Item" />
    <input type="reset" value="Reset" />
    </form>