Mark Lee Mark Lee - 4 months ago 19
PHP Question

How to execute a foreach loop in a query to correctly display selected options in a list

I have seven columns representing days of the week in a user table 'team' storing the indices from nine daily ranges from which the user selects. Table timerange range uses the index rangeID. The user chooses from a multiple select list one or more timeranges and the indices are stored for that day as in comma delimited list:


+------+-------+---------+

|sun   + mon + tue     +

|-------+-------+---------+

|2,4,5 + 1,3   +  9       +

+------+-------+---------+


My challenge is to return a div table of the same presentation of that used to select the date ranges but with the selected options highlighted (selected) so that the user's availability can be displayed and or edited in the form.



I have attempted to get that result with the following query for one day, Sunday, but am failing with the result that while the selected options display correctly, each of the choices not selected displays twice in the select lst:



echo "<select multiple=\"multiple\" size=\"10\" name=\"sun[]\">";
echo "<option size=\"30\"></option>";
$sql = "SELECT sun, rangeID, timeranges FROM test, timerange ORDER BY rangeID";
foreach ($myconnect->query($sql) as $row) {
$index=explode(',', $row['sun']);
foreach($index as $sun) {
if ($row['rangeID']==$sun) {
echo "<option selected=\"selected>\" value='" . $row['rangeID'] . "'>" . $row['timeranges'] . "</option>";
} else {
echo "<option value='" . $row['rangeID'] . "'>" . $row['timeranges'] . "</option>";
}
}
}
echo "</select>";


Somewhere logic is failing me so I'd like help in correcting the code so that only the nine time ranges are displayed with zero, one or more options selected.

Answer

After a week of all sorts of gymnastics with code, adding to my gray hairs, I arrived at a solution that is not elegant but is fine since the action is to be called by one user (at a time) to pre-populate a form. It beats creating multiple tables, which will have many empty rows, to store 0-9 indexes of nine indexes. I'll definitely work on a more efficient table structure for storing the user-selected data and querying one or multiple users at a time.

echo "<select  multiple=\"multiple\" size=\"9\"  name=\"sun[]\">";
$sql = "SELECT sun, rangeID, timeranges FROM test, timerange ORDER BY rangeID";
foreach ($myconnect->query($sql) as $row)
{
  $index = explode(',', $row['sun']);
  for ($x = 0; $x <= 4; $x++)
  {
    if ($row['rangeID'] == $index[0] or $row['rangeID'] == $index[1] or $row['rangeID'] == $index[2] or $row['rangeID'] == $index[3])
    {
      $selected = "selected=\"selected\"";
    }
    else
    {
      $selected = "";
    }
  }
  echo "<option value=" . $row['rangeID'] . " " . $selected . ">" . $row['timeranges'] .
  "</option>";
}
echo "</select>";

I'd appreciate suggestions on how to iterate through the incremental values of a "for" loop so I can use if ($row['rangeID'] == $index[$x] instead of the recurring "or". I haven't been able to grasp how to use count() to do this.

Comments