Forums

Generate Select options from a table in database

Permalink
Hi All

We are currently developing a page management system using concrete5 but we are having some problems with generating a select box from a table in our database.

We need to have a select option on our page which needs to be populated from a column within our database via a controller. we have multiple values in our database all with unique id's which need to populate into the value attribute within each option of the select box.

We have a starting point from looking at previous code, however nothing is working:

Controller - select_county.php
<?php defined('C5_EXECUTE') or die("Access Denied.");
function view() {
      $db = Loader::db();
      $q = "SELECT county_id, county_name FROM lfm_county WHERE county_active = 1";
      $r = $db->Execute($q);
      $list = new SelectAttributeTypeOptionList();
      $i = 0;
      while ($row = $r->FetchRow()) {
         $opt = new SelectAttributeTypeOption($row['county_name'], $row['county_id'], $i);
         $list->add($opt);
         $i++;
      }      
      return $list;
   }
?>


Live page 
<?  
   $controller = Loader::controller('/select_county'); 
   $tagCounts = array();
   $ttags = $row;
   $tags = array();
   foreach($ttags as $t) {
      $tagCounts[] = $t->getSelectAttributeOptionUsageCount();
      $tags[] = $t;
   }
?>


If anyone has any useful info, that would be great as all i have been able to find are select options associated with page lists.
simonknibbs
 
formigo replied on at Permalink Reply
formigo
I'm not sure what you've got there but looks like you had a good go at it.

I'd do it like this. Not tested so forgive any syntax issues or omissions.

<?php 
// Get data
$db = Loader::db();
$q = "SELECT county_id, county_name FROM lfm_county WHERE county_active = 1";
$counties = $db->getAll($q);
// Make an array we can pass to form helper select. 
$selectOptions = array();
foreach($counties as $county) {
   $selectOptions[$county['county_id']] = $county['county_name'];   
}
// Load form helper
$form = Loader::helper('form');
// Output select box via form helper. Asssumes new form - no initial value set.
echo $form->select('county', $selectOptions); 
?>


Hope that points you in the right direction

Best

Ollie
simonknibbs replied on at Permalink Reply
simonknibbs
Thanks for your help, we have decided to go with this option, it just seemed like alot of code of code to write each time, we didn't know whether their was a slicker way to include it using the controller.

<label><?php   echo t('County') ?> <span class="required">*</span></label>
<select id="county_id" name="county_id" autocomplete="off">
    <?php
    $db = Loader::db();
    $sql = "SELECT county_id, county_name FROM lfm_county WHERE county_active = 1 ORDER BY county_name ASC";
    $r = $db->Execute($sql);
    $row = $r->FetchRow();
    foreach($r as $row) { 
        echo '<option value="'.$row['county_id'].'" '.($customObject->getTownCountyID() == $row['county_id'] ? 'selected="selected"' : '').'>'.$row['county_name'].'</option>';
    }
    ?>
</select>