Greyhead.net

One frequent request is to create a select drop-down from a set of records in a database table.

Getting ready

You'll need to know the MySQL query to extract the records you need from the database table. This requires some Joomla! knowledge and some MySQL know-how.

Joomla! keeps its articles in the #__content database table. (NB in Joomla!1.6+ table prefixes are random strings so we use the #_ syntax here, JJoomla!will automatically replace this with the correct prefix.) The two table columns that we want are the article title and the article id. A quick check in the database tells us that the columns are appropriately called title and id. We can also see that the section id column is called sectionid (with no spaces); the column that tells us if the article is published or not is called state and takes the values 1 for published and 0 for not published.

How to do it . . .

We are going to use some PHP to look up the information about the articles in the database table and then output the results it finds as a series of options for our drop-down box.

You'll recall that normal HTML code for a drop-down box looks something like this:

<select . . .>
  <option value='option_value_1'>Option 1 text</option>
  <option value='option_value_2'>Option 2 text</option>
  . . .
</select>

This is simplified a little so that we can see the main parts that concern us here – the options. Each option uses the same code with two variables – a value attribute and a text description. The value will be returned when the form is submitted; the text description is shown when the form is displayed on your site.

In simple forms, the value and the description text are often the same. This can be useful if all you are doing with the results is to display them on a web page or in an e-mail. If you are going to use them for anything more complicated than that, it can be much more useful to use a simplified, coded form in the value.

For our list of articles, it will be helpful if our form returns the ID of the article rather than its title. Hence, we need to set the options to be something like this:

<option value='99'>Article title</option>

Having the article ID will let us look up the article in the database and extract any other information that we might need, or to update the record to change the corresponding record.

Here's the first version of the code that we'll use. First drag a Custom Element into the Preview box and click the 'spanner' icon to open the Configuration dialogue. Then you can type (or copy and paste) the following snippet into the Code box.

The PHP snippet looks up the article IDs and titles from the #__content table, then loops through the results writing an <option> tag for each one. In this version we collect the <option> tags in an array and then use implode() to turn them back into a list. This isn't essential but sometimes it makes the coding and the resulting Form HTML presentation tidier and easier to understand.

<?php
$options = array();
$options[] = "<option value=''>--?--</option>";
$db =& JFactory::getDBO();
$query = "
  SELECT `id`, `title`
    FROM `#__content`
    WHERE `sectionid` = 0
      AND `state` = 1  
      ORDER BY `title`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
foreach ( $data as $d ) {
  $options[] = "<option value='{$d->id}'>{$d->title}</option>";
}
?>
<select class="" id="articles" size="1" name="articles">
<?php echo implode("\n", $options); ?>
</select>

The resulting HTML will be a standard select drop-down that displays the list of titles and returns the article ID when the form is submitted. Here's what the form input looks like:

Note that the label text is added in the Label box of the Custom Code element.

A few of the options from the page source are shown:

<option value='1'>Welcome to Joomla!</option>
<option value='2'>Newsflash 1</option>
<option value='3'>Newsflash 2</option>
. . .

We need to add one more small piece to this to make sure that if the form is redisplayed then any previously selected option will be shown as selected. For this we need to add to the foreach loop in our code:

foreach ( $data as $d ) {
  $selected = '';
  if ( isset($form->data['articles']) && $form->data['articles'] == $d->id ) {
    $selected = "selected='selected'"; 
  }
  $options[] = "<option value='{$d->id}' $selected >{$d->title}</option>";
}

Note: while you could use a DB Multi Record Loader action for this it isn't necessary and this approach is more easier to use. 

How it works...

We are loading the values of the id and title columns from the database record and then using a PHP for each loop to go through the results and add each id and title pair into an <option> tag.

There's more...

There are many occasions when we want to add select drop-downs into forms with long lists of options. Date and time selectors, country and language lists, and many others are frequently used.

We looked here to get the information from a database table which is simple and straightforward when the data is in a table or when the data can conveniently be stored in a table. It is the preferred solution for data such as article titles that can change from day to day.

There are a couple of other solutions that can also be useful:

• Creating numeric options lists directly from PHP
• Using arrays to manage option lists that change infrequently

Creating numeric options lists

Let's imagine that we need to create a set of six numeric drop-downs to select: day, month, year, hour, minute, and second. We could clearly do these with manually-created option lists but it soon gets boring creating sixty similar options.

There is a PHP method range() that lets us use a similar approach to the one in the recipe. For a range of zero to 60, we can use range(0, 60). Now, the PHP part of our code becomes:

<select class="cf_inputbox validate-selection" id="minutes" size="1" name="minutes">
  <option value=''>--?--</option>
<?php
foreach ( range(0, 60) as $v ) {
  echo "<option value='$v'>$v</option>";
}
?>
</select>

This is slightly simpler than the database foreach code, as we don't need the quotes round the array values.

This will work very nicely and we could repeat something very similar for each of the other five drop-downs. However, when we think about it, they will all be very similar and that's usually a sign that we can use more PHP to do some of the work for us.

Indeed we can create our own little PHP function to output blocks of HTML for us.

Looking at this example, there are four things that will change between the blocks – the label text, the name and id, and the range start and the range end. We can set these as variables in a PHP function:

<?php
function createRangeSelect($label, $name, $start, $end) {
  $options = array();
  $options[] = '<option value="">--?--</option>';
  foreach ( range($start, $end) as $v ) {
    $options[] = "<option value='$v'>$v</option>";
  }
  echo "<div style='clear:both;' >";
  echo "<label class='cf_label' style='width: 150px;'>{$label}</label>";
  echo "<select class='validate[\"required\"]' id='{$name}' size='1' name='{$name}' >";
  echo implode("\n", $options);
  echo '</select></div>';
}
?>

Notice that this is very similar to the previous code example. We've added the function . . . line at the start, the } at the end, and replaced the values with variable names.

It's important to get the placement of the <?php . . . ?> tags right. Code inside the tags will be treated as PHP, outside them as HTML.

All that remains now is to call the function to generate our drop-downs:

<?php
createRangeSelect('Day', 'day', 0, 31);
createRangeSelect('Month', 'month', 1, 12);
createRangeSelect('Year', 'year', 2000, 2020);
createRangeSelect('Hour', 'hour', 0, 24);
createRangeSelect('Minute', 'minute', 0, 60);
createRangeSelect('Second', 'second', 0, 60);
function createRangeSelect($label, $name, $start, $end) {
. . .

The result tells us that we have more work to do on the layout, but the form elements work perfectly well.

Creating a drop-down from an array

In the previous example, we used the PHP range() method to generate our options. This works well for numbers but not for text. Imagine that we have to manage a country list. These do change, but not frequently. So they are good candidates for keeping in an array in the Form HTML.

It's not too difficult to find pre-created PHP arrays of countries with a little Google research and it's probably easier to use one of these and correct it for your needs than to start from scratch.

As we mentioned with the Article list, it's generally simpler and more flexible to use a list with standard IDs (we've used two-letter codes below). With countries, this can remove many problems with special characters and translations.

Here are the first few lines of a country list:

$countries = array(
  'AF'=>'Afghanistan',
  'AL'=>'Albania',
  'DZ'=>'Algeria',
  'AS'=>'American Samoa',
  . . .
);

Once we have this, it's easy to modify our foreach . . . loop to use it:

foreach ( $countries as $k => $v ) {
  $option[] = "<option value='$k'>$v</option>";
}

If you are going to use the country list in more than one form, then it may be worthwhile keeping it in a separate file that is included in the Form HTML. That way, any changes you make will be updated immediately in all of your forms.

---

There are different styles of laying out both JavaScript and PHP and sometimes fierce debates about where line breaks and spaces should go. We've adopted a style here that is hopefully fairly clear, reasonably compact, and more or less the same for both JavaScript and PHP. If it's not the style you are accustomed to, then we're sorry.