Generate Select options from a table in database
PermalinkWe 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
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.
<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>
I'd do it like this. Not tested so forgive any syntax issues or omissions.
Hope that points you in the right direction
Best
Ollie