Category: MySQL

The following code will take the contents of the first row of a configuration table and create a constant for each attribute stored in it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// ----------------------------- LOAD CONFIGURATION DATA FROM DATABASE -----------------------------
// Each data element in first row is placed in a constant using attribute name as constant name
// The array_slice function eliminates the first attribute in the row, 'id', which is the primary
//   key and not relevant for our purposes.
$result = mysql_query("SELECT * FROM configuration WHERE id = '1'") or die(__LINE__." SELECT Error: ".mysql_error());
if($result != false and mysql_num_rows($result)) {
    foreach(array_slice(mysql_fetch_assoc($result), 1) as $key => $val) {
        if(!defined($key)) {
            define(strtoupper($key), $val);
        } else {
            user_error("'$key' already defined.");
        }
    }
}
// ---------------------------- /load configuration data from database -----------------------------

A common scenario in web applications is the need to create a <SELECT> list using a set of values extracted from a lookup table in a database. This article will explore a simple example of how to do that.

For this example, imagine that in our database we have a lookup table, lu_status, that holds all the possible values we will use for the status field in our form.  Imagine also the the attribute name in our form is 'status', and that our lu_status table has two attributes; status, which is the value and option_label which will be the visible label in the select control drop list. Because we might be using the <select> list in a form to edit existing records in a data table, we can also assume that the current value of the status attribute, if any, is in the variable $status.

In line 2, we create a variable, $select_status, and put the first line of the code for our select control in it.  The name 'status' in that line is the attribute name in the data table that the form will be feeding. The \n at the end of the line will cause a line break in the source code of the HTML page, making it easier to read and troubleshoot.

Line 3 is the default –SELECT ONE– value commonly used in forms when no value has previously been selected.

Lines 4-12 are where we access that database and get to work.

In line 4, we read all the possible values from the lookup table which we will use to populate our select list.

Line 5 opens a while loop which we will use to process each row that we read from the lookup table in the previous row, creating a <option> element for each.

Line 6 opens the <option> element.

Lines 7-11 are an if statement which will determine if the current value of the status attribute (if there is a current value) matches the current status attribute of the current row from the lookup table. If so, it adds the attribute selected='selected' to the option element, otherwise it creates the element without the selected attribute. In either case, the status attribute and option_label attribute from the current row are inserted into the <option> element appropriately. Each row in the table lu_status will be processed this way inside the while loop.

Now that we've build all our <option> elements, all that remains is to close the <select> control in line 13 and we're done.

Here's the PHP code;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// assemble the HTML code for the <select> list in a variable called $select_status
$select_status = "<select name='status'>\n";
$select_status .= "<option value=''>SELECT ONE</option>\n";
$dataset = mysql_query("SELECT * FROM lu_status");
while($row=mysql_fetch_array($dataset)) {
  $select_status .= "<option value='".$row['option_label']."'";
  if (strtolower($row['status']) == strtolower($status)) {
    $select_status .= " selected='selected'>".$row['option_label']."</option>\n";
  } else {
    $select_status .= ">".$row['option_label']."</option>\n";
  }
} // end while loop
$select_status .= "</select>\n";
// now insert the <select> list control into the page
echo "$select_status";

And here's the HTML code we will see generated;

1
2
3
4
5

<select name='status'>
  <option value=''>SELECT ONE</option>
  <option value='active' selected='selected'>Active</option>
  <option value='inactive'>Inactive</option>
</select>