I am trying to get the category headings of each product in a dropdown list, with all the categories products as subs, these are all linked to a customer.
I have 2 tables in my database, one which contains the categories and products and then the other has the customer information that has all the products linked to the customer.
I am not sure where i am going wrong in the below code. The output is as follows:
Category 1
- Product 1
Category 2
- Product 2
Category 3
- Product 3
Category 2
- Product 4
Whereas i need it to be as below:
Category 1
- Product 1
Category 2
Product 2
Product 4
Category 3
- Product 3
Code
<?php
$customer_id = "27";
?>
<select>
<?php
$records = mysqli_query($conn_register, "SELECT * FROM customer_product WHERE customer LIKE '$customer_id '");
while ($get_value = mysqli_fetch_array($records)) {
$product = $get_value['product'];
$sql_1 = "SELECT * FROM addproducts WHERE `name` LIKE '$product'";
$result_1 = $conn_register->query($sql_1);
$data = $result_1->fetch_assoc();
$groups[$data['category']][$get_value['product']] = $get_value['product'];
foreach ($groups as $label => $opt) { ?>
<optgroup label="<?php echo $label; ?>">
<?php foreach ($opt as $id => $name) { ?>
<option value="<?php echo $name; ?>"><?php echo $name; ?></option>
<?php } ?>
</optgroup>
<?php
}
}
?>
My Answer:
// Assuming you have already connected to your database
// Query to get all categories and products
$query = "SELECT c.category_name, p.product_name
FROM categories c
JOIN products p ON c.category_id = p.category_id
ORDER BY c.category_id";
$result = mysqli_query($connection, $query);
// Initialize variables to keep track of current category
$current_category = "";
?>
Rate this post
5 of 5 based on 4357 votesComments