Mark Lee Mark Lee - 1 year ago 110
MySQL Question

Limiting Query Result from a for each Loop

I have three tables in a MySQL database, from which I am populating a dropdown select box. The problem arises because I want to use a line of the result as an option group label. However what I've achieved is the optgroup recurring with each option.

The tables are category, skill and skill_category. Category is a list of categories and their IDs (category_id, category_name); skill is a list of skills and their IDs (skill_id, skill_name) and skill_category assigns skills to categories (skill_id, category_id).

This is the code I'm using to retrieve and display the list of skills in a form to be grouped by their category names rather than a long list not visually broken up:

echo "<select name=\"skills\">";
echo "<option size =30 ></option>";
$sql = "SELECT * FROM skill " .
"LEFT OUTER JOIN skill_category ON skill.skill_id=skill_category.skill_id " .
"LEFT OUTER JOIN category ON skill_category.category_id=category.category_id"
foreach ($myconnect->query($sql) as $row){
echo " <optgroup label='" . $row['category_name'] ."'>";
echo "<option value='" . $row['skill_name'] ."'>"
. $row['skill_name'] .

The question is how to begin the loop that fetches the
along with the skill list but not have the category name atop every single skill. I believe the answer is staring me in the face but I can't see it. I don't want to manually write each optgroup and loop for each set of skills

Answer Source

Try storing the previously used category name in a variable and compare each iteration of the for loop. If the value is different, that means it's a category that hasn't been used before.

echo "<select name=\"skills\">";
echo "<option size=\"30\"></option>";
$sql = "SELECT * FROM skill LEFT OUTER JOIN skill_category ON skill.skill_id=skill_category.skill_id
LEFT OUTER JOIN category ON skill_category.category_id=category.category_id ORDER BY category_name";
$previousCategoryName = "";
foreach ($myconnect->query($sql) as $row) {
  if ($previousCategoryName != $row['category_name']) {
    if ($previousCategoryName != "") {
      echo "</optgroup>";
    echo "<optgroup  label='" . $row['category_name'] ."'>";
    $previousCategoryName = $row['category_name'];
  echo "<option value='" . $row['skill_name'] ."'>" . $row['skill_name'] ."</option>";
echo "</optgroup>";

Note I added the ORDER BY category_name into the query