Creating A Function Question

[kathymack]

Hi - I have written code that populates a dropdown from my table.I call this code quite a few times over my app and was wondering is that somewayI can turn this in to a function? I don't know if returning 2 arrays is the right way to go.Looking for suggestions.

 

$sQuery   = "SELECT * FROM `drop-business-categories` ORDER BY `item` ";
$iResult  = $mysqli->query($sQuery);
$iNumRows = $iResult->num_rows;
$iCount   = 0;
if ($iNumRows > 0)
{
  while ($iRow = $iResult->fetch_row())
  {
   $sArrayAllCatID[$iCount]   = $iRow[0];
   $sArrayAllCatItem[$iCount] = $iRow[1];
   $iCount++;
  }
}

[PFMaBiSmAd]

Since the id and the item are directly related to each other, why not use the id as the array index and the item as the array value. That gives one array holding all the related data.

[kathymack]

This is probably a dumb question, but the id can be any numeric value so how would I set this up?

[DavidAM]

When I'm doing drop-downs, I return an associative array, with the database unique ID as the key and the value as the, well ... value.

 

function dropdownList($dbConn, $table, $idCol, $valueCol) {
 $sql = "SELECT $idCol, $valueCol FROM $table ORDER BY $valueCol";
 $res = $dbConn->query($sql);
 if (! $res) {
   # For development ...
   trigger_error('dropdownList query failed with error: ' . $dbConn->error() . ' -- ' . $sql, E_USER_ERROR);
   # ... in Production we need to handle the error or return an empty array
   return array();
 }
 $out = array();
 while ($row = $res->fetch_assoc()) {
   $out[$row[$idCol]] = $row[$valueCol];
 }

 return $out;
}

 

Disclaimer: Off the top of my head. Not Tested

 

To use the data:

 

$list = dropdownList($mysqli, 'Categories', 'ID', 'CategoryName');

$html = '<SELECT name="category">';
foreach ($list as $id => $value) {
 $html .= '<OPTION value="' . $id . '">' . htmlspecialchars($value) . '</OPTION>';
}
$html .= '</SELECT>';

echo $html;

 

Something like that

[PFMaBiSmAd]

This is probably a dumb question, but the id can be any numeric value so how would I set this up?

 

 while ($iRow = $iResult->fetch_row())
{
$array[$iRow[0]] = $iRow[1];
}