Accessing the DB from an HTML Form

Contents

Interacting With the Sahana DB

In this example we will create a table to be used with the helloworld module. The revised module can be downloaded.

Setting Up

You will want to run your installation of phpmyadmin. On my Mac it is installed as http://localhost/~rmorelli/phpmyadmin/. PhpMyadmin sets up a GUI interface to the Sahana database.

Creating a Database Table

In PhpMyAdmin go to the Database drop-down menu and open the Sahana database. On my system it currrently contains 97 tables.

To create a new table, select to the Operations menu. Let's make a table called hw_greetings. This table will have two fields, one named type, which takes varchar data, and the other named greeting, which takes text data.

Once that table is created, you will want to put some values into it. The following table gives some example values:

TypeGreeting
helloHello there
helloIt's good to see you
helloAloha
goodbyeSee you later
goodbyeSayanara
goodbyeLater
getwellGet well quick
getwellHope you're feeling better


Use the insert menu to insert several rows of greetings into the table. As you can see from this example, there are several greetings of each type. In our revised form, we will only use the greetings, not their types. To see the data stored in your table, you can use the browse menu.

Accessing the DB Table in Your Form

We will modify the sayHello() function to create a drop-down menu that will allow the user to select the type of greeting to present. We will use the Sahana library function shn_form_select() which takes several arguments. The important ones are the first three:

  • $options -- an array containing the options to display in the menu
  • $label -- the prompt label
  • $name -- the name of the HTML element, to be used in the $_POST

The first argument, $options should be an array of elements of the form [value, description]. The way this function works, the description will be displayed in the drop-down menu, and the value will be returned as the value of the POST operation. In our case, we'll construct an array of the form [greeting, greeting], that is, we'll use the description itself as the HTML element's value.

To help us do this, we write the following function:

function get_field_data_from_db($table, $field) {
    global $global;
    $q = "select $field from $table";             // Construct the query
    $result = $global['db']->Execute($q);         // Get the data from the DB
    $options = array();                           // Create an array for the options
    while (!$result == NULL && !$result->EOF) {
        $options[$result->fields[0]] = $result->fields[0];  // E.G., $options['hello']='hello';
        $result->MoveNext();
    }
    return $options;
} // get_field_data_from_db()

We will call this function so that it constructs the following very simple DB query:

    Select greeting from hw_greetings

This result of this query will be a sequence of objects each of which contains an array of fields, indexed 0 through n-1, where n is the number of fields included in our query. In our case, there is only one field, greeting, so our data is stored in fields[0]. We simply loop through the sequence of objects and construct an array of the form:

  $options['hello']='hello';
  $options['goodbye']='goodbye';
  ...

The loop shown here is the standard way to retrieve results from a DB query.

The function then returns the array. To use this array in the say_hello() we would add the following lines of code to that method:

    $options = get_field_data_from_db("hw_greetings", "greeting");     // Create an array of options
    shn_form_select($options, $label="Choose a greeting:", $name='greeting');  // Create the drop-down menu
    echo "<br>";

In this case the name of the form element is greeting. Therefore, to process this element in process_hello(), we need only add the following line of code:

    $greeting = $_POST['greeting'];

We can then replace the literal greeting "Hello" with our variable greeting.

Possible Modifications and Enhancements

To extend this example, you might try several variations and enhancements. Here are some possibilities:

  • Create a more elaborate hw_greetings table.
  • Use the type field in the drop-down menu and allow the user to select a type of greeting. Then, in process_hello(), use the type to query the hw_greetings table and retrieve all greetings of that type. Then randomly choose one of those greetings as your greeting.
  • Allow the user to construct their own custom greeting. When the form is submitted, do a DB query to insert the new query into the hw_greetings table as type custom.
  • Modify the get_field_data_from_db() function so that it optionally selects only certains types of greetings, e.g., hello greetings.
  • Use the type field in the drop-down menu, but once the user selects the type, change the contents of a second drop-down menu, to show greetings of that type. This is similar to choosing the state in one drop-down and getting a different list of cities in a second drop-down. (This is much more challenging.)
In any case, have fun!