Grouping items by their category works but its not placing all the products of the category in one optgroup but repeating it.
All the items should be in one group not separate as an duplicate group.
<select style="width: 25%;" name="item[]" id="item1" class="item browser-default custom-select-new">
<option value="" disabled selected>Click to See Products</option>
<?php
$conn_register = mysqli_connect('localhost', 'root', '', 'register');
$Comid = "27";
$levy_input = "";
$records = mysqli_query($conn_register, "SELECT * FROM customer_product WHERE customer LIKE '$Comid'");
while ($data = mysqli_fetch_array($records)) {
$price = $data['new_total_rate'];
$product = $data['product'];
$sqlii = "SELECT DISTINCT category
FROM addproducts
WHERE `name` LIKE '$product'
ORDER BY 'category' ";
$resultii = $conn_register-> query($sqlii);
$prof= $resultii -> fetch_assoc();
$Pcategory = $prof["category"];
echo '<optgroup label="'. $Pcategory .'">';
echo '<option value="' . $data['product'] . '"
data-new_price_rate="' . $data['new_total_rate'] . '"
data-description="' . $data['description'] . '"
data-tax="' . $data['tax'] . '"
data-PriceRate_NoDiscount="' . $data['new_price_rate'] . '">'
. $data['product'] . '</option>
</optgroup>';
}
?>
</select>
2
Answers
I have solved the problem, i joined the two query's with a left join and added a if statement to prevent repeating in the while loop.
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 anORDER 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 theORDER 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
: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 thecp
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 toFALSE
and set toTRUE
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.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 withPDOStatement->setAttribute
).