Option Grouping in Drop-Down from MySQL results

Question

I am attempting to create option groups for a dropdown selection form, but I am receiving the following error at the $innerRow = mysqli_fetch_array($innerResults) line.

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

Code

$options = "";
$query = "SELECT DISTINCT manufacturer_name 
          FROM product p 
          JOIN manufacturer m 
          ON p.manufacturer_id = m.manufacturer_id";
$outerResults = mysqli_query($con, $query);
while ($outerRow = mysqli_fetch_array($outerResults)) {
    $options .= "<optgroup label='{$outerRow["manufacturer_name"]}'>";

    $query = "SELECT product_id, model_number
              FROM product
              WHERE manufacturer_id 
              IN (SELECT manufacturer_id 
              FROM manufacturer 
              WHERE manufacturer_name = {$outerRow["manufacturer_name"]}";
    $innerResults = mysqli_query($con, $query);
    while ($innerRow = mysqli_fetch_array($innerResults)) {
        $options .= "<option value='{$innerRow["product_id"]}'>{$innerRow["model_number"]}</option>";
    }
    mysqli_free_result($innerOptions);
}
mysqli_free_result($outerOptions);
mysqli_close($con)

Answer 1

First, the reason your inner queries are failing is because you have not wrapped your {$outerRow["manufacturer_name"]} in single quotes as string values require and you didn't close your subquery's parenthesis: '{$outerRow["manufacturer_name"]}'); But I don't want you to fix your query, please keep reading...

There are a few things I'd like to correct about your process:

• Your first query doesn't utilize the true power of MySQL. You really should not be making a new query for each manufacturer in your database -- it is inefficient.
• I recommend that you declare and condition-check your query's resultset to avoid WARNING messages and handle a false query appropriately.
• You are not closing your <optgroup> tags during your dom build. Your output will still render as desired, but the tags are meant to be closed.
• Because you are not using the numeric keys from your resultsets, you should be using mysqli_fetch_assoc() instead of mysqli_fetch_array().

This is a complete rewrite of your code, which will make the above corrections and refine your querying process down to a single call:

require_once("db.php");
$sql="SELECT M.manufacturer_name,P.product_id,P.model_number 
      FROM manufacturer M 
      LEFT JOIN product P ON M.manufacturer_id=P.manufacturer_id 
      GROUP BY M.manufacturer_id,P.product_id
      ORDER BY M.manufacturer_name,P.model_number;";    
if($result=mysqli_query($con,$sql)){
    if(mysqli_num_rows($result)){
        $last_group=null;
        $select="<select name=\"products\">";
            while($row=mysqli_fetch_assoc($result)){
                if($row["manufacturer_name"]!=$last_group){
                    $select.=($last_group!=null?"</optgroup>":"")."<optgroup label=\"{$row["manufacturer_name"]}\">";
                    $last_group=$row["manufacturer_name"];
                }
                if($row["product_id"]==null){
                    $select.="<option disabled>No Products</option>";
                }else{
                    $select.="<option value=\"{$row["product_id"]}\">{$row["model_number"]}</option>";
                }
            }
        $select.="</optgroup></select>";
        echo $select;
        mysqli_free_result($result);
    }else{
        echo "Empty Resultset From Query";  
    }
}else{
    echo mysqli_error($con);    
}

I tested the above code using these table structures and data:

CREATE TABLE `product` (
  `product_id` int(10) NOT NULL,
  `manufacturer_id` varchar(20) NOT NULL,
  `model_number` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `product` (`product_id`, `manufacturer_id`, `model_number`) VALUES
(1, '1', '#0001'),
(2, '1', '#0002'),
(3, '1', '#0003'),
(4, '2', '#0001'),
(5, '2', '#0002'),
(6, '4', '#0001');

ALTER TABLE `product`
  ADD PRIMARY KEY (`product_id`);

ALTER TABLE `product`
  MODIFY `product_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

AND

CREATE TABLE `manufacturer` (
  `manufacturer_id` int(10) NOT NULL,
  `manufacturer_name` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `manufacturer` (`manufacturer_id`, `manufacturer_name`) VALUES
(1, 'AAAA'),
(2, 'BBBB'),
(3, 'CCCC'),
(4, 'DDDD');

ALTER TABLE `manufacturer`
  ADD PRIMARY KEY (`manufacturer_id`);

ALTER TABLE `manufacturer`
  MODIFY `manufacturer_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

My code block will output this:

<select name="products">
    <optgroup label="AAAA">
        <option value="1">#0001</option>
        <option value="2">#0002</option>
        <option value="3">#0003</option>
    </optgroup>
    <optgroup label="BBBB">
        <option value="4">#0001</option>
        <option value="5">#0002</option>
    </optgroup>
    <optgroup label="CCCC">
        <option disabled>No Products</option>
    </optgroup>
    <optgroup label="DDDD">
        <option value="6">#0001</option>
    </optgroup>
</select>

Which renders as:

p.s. If you don't want the product-less manufacturers in your resultset (or select options list), write HAVING P.product_id IS NOT NULL between the GROUP BY and ORDER BY lines/clauses.