Note that you unconditionally output an <optgroup> with each iteration; thus, if you don't want to output each time through, you'll need to (at the very least) make the output conditional. In many circumstances this wouldn't be sufficient, as sequential rows may be in different groups; instead, you'd need to group the results in PHP, and then iterate over the results. Here, with a few alterations an ORDER BY clause will ensure the items in each category are processed sequentially. The current statements aren't sufficient, as the categories are retrieved in a separate statement, which not only prevents the ORDER BY from making items in a category contiguous but is inefficient, as it makes many more requests than necessary.
The two statements can be combined with a JOIN:
SELECT cp.product AS name,
cp.description,
cp.tax,
cp.new_price_rate,
cp.new_total_rate,
ap.category
FROM customer_product AS cp
LEFT JOIN addproducts AS ap ON cp.product = ap.name
WHERE customer LIKE ? -- NB: parameter for prepared statement
ORDER BY ap.category
If there's a 1:N relationship between customer_product.product and ap.name, then the query will need to be modified so as to only return one row for each customer_product row (a simple approach is to group by all the cp rows).
To determine when to output an <optgroup>, the code will need to detect when the category changes. This is easily accomplished by storing the current category, and comparing it to the category for a new row.
Generally speaking, you must consider what happens on the first, intermediate and last iterations.
The first iteration is important in that you want to force the output of an optgroup. As long as the stored category will never equal a category from the DB, this is essentially automatic.
Additionally, no </optgroup> close tag should be output on the first iteration. A simple way to skip this the first time through is to use a variable to hold the tag to output, which is initialized to just the open tag, and then add the close tag the first time an <optgroup> is output. Another is to have a flag recording whether there's an optgroup to close (initialized to FALSE and set to TRUE when <optgroup> is output), and outputting the close tag only if the flag is true.
After the last iteration, the last <optgroup> must be closed. Under the assumption that there's at least 1 product in 1 category, you should be able to unconditionally output a close tag after the loop.
Note that the sample code in the question mixes many different kinds of tasks, primarily DB access and output. This violates separation of concerns. Instead, each should be placed in separate modules. The exact methodology to achieve this is well beyond the scope of this Q&A, but a simplified method is used in the sample code below.
Be sure to encode any non-HTML strings using htmlspecialchars, both to prevent injection and broken HTML.
<?php
// initially, there's no category
$category = NULL;
// the category tag; will be updated to close the previous element the first time it's output
$catTag = '<optgroup ';
// attributes for each <option> element
$attrs = [
'value' => 'name',
'data-new_price_rate' => 'new_total_rate',
'data-description' => 'description',
'data-tax' => 'tax',
'data-PriceRate_NoDiscount' => 'new_price_rate',
];
?>
<select>
<option disabled>Click to see products</option><!-- Note: this smells a bit. -->
<?php
// note there's no sign of DB access; the products could come from anywhere
foreach ($customerProducts->fetch($comid) as $product) {
// The core of the answer: output a new optgroup only then when the category changes
if ($category != $product['category']) {
echo $catTag, 'label="', htmlspecialchars($product['category']), "\">\n";
// from now on, close the previous element when there's a new optgroup
$catTag = "</optgroup>\n<optgroup ";
}
// output the current product as an option element
?>
<option<?php foreach ($attrs as $attr => $prop) {
echo ' ', $attr, '="', htmlspecialchars($product[$prop]), '"';
} ?>><?= htmlspecialchars($product['name']) ?></option>
<?php
$category = $product['category'];
}
?>
</optgroup>
</select>
The sample assumes that every product is in 1 category, so $product['category'] isn't null. However, if any product isn't in a category, it should still work unless every product in the results is in no category, in which case there will be no <optgroup>s and the final </optgroup> won't close anything, producing invalid HTML.
The following (untested) sample is only for the purposes of separating DB access from the HTML generation. Search elsewhere for information on topics such as DALs, prepared statements, and the Traversable interface (which is supported by mysqli_result, though PDO has better support, including allowing you to set the result type to something other than an associative array with PDOStatement->setAttribute).
class CustomerProducts {
static $statements = [
'read' => 'SELECT […]',
];
function __construct($db) {
$this->db = $db;
$this->read = $db->prepare(self::$statements[read]);
}
function fetch($id) {
$this->read->bind_param('i', $id);
if ($this->read->execute()) {
return $this->read->get_result();
} else {
// handle failure with e.g. an exception
throw …;
}
}
}
