从数组中的数据库中获取多行

Question

With code below I get id of category and name of products in that category. Category id is echoed and under it select is shown with products names for that category. Now it looks like this:

<label>37</label> <!--category id-->
<select>
<option>product one</option>
<option>product two</option>
</select>

But I need it to show category name an in option value product id. Like this:

<label>category name</label>
<select>
<option value="1">product one</option>
<option value="2">product two</option>
</select>

How can I achieve that in code below?

    $categories = array();
    $sql= "
SELECT a.id
     , a.name
     , a.active
     , b.id product_id
     , b.name product_name
     , b.flag_active
     , c.product_id
     , c.group_id 
  FROM products_groupnames a 
  JOIN products_group c 
    ON a.id = c.group_id 
  JOIN products_name b 
    ON b.id = c.product_id
 WHERE a.active=1 
   AND b.flag_active = 1
";
    $result = $mysqli->query($sql);
    echo "<h4>Select products by groups</h4>";
    while($row = $result->fetch_assoc()) {
        $categories[$row['id']][] = $row['product_name'];
    }

    foreach($categories as $key => $category){
        echo '<label>'.$key.'</label><br/>';
        echo '<select>';
        foreach($category as $item){
            echo "<option>".$item."</option>";
        }
        echo "</select><br/>";
    }

Answer 1

Make your $categories array 2-dimensional:

while ($row = $result->fetch_assoc()) {
    $cat = $row['id'];
    if (isset($categories[$cat])) {
        $categories[$cat]['products'][] = $row;
    } else {
        $categories[$cat] = array('name' => $row['name'], products => array($row));
    }
}

Then you can display it using nested loops:

foreach ($categories as $category) {
    echo '<label>'.$category['name'].'</label>';
    echo '<select>';
    foreach ($category['products'] as $prod) {
        echo '<option value="'.$prod['product_id'].'">'.$prod['product_name'].'</option>';
    }
    echo '</select><br/>';
}