The product(s) table should have a category_id column (the other tables which have a column that holds a foreign id, should also be named xxxxx_id so that you can tell what the query is doing without confusion.) The above JOIN condition would be - ON products.category_id = product_categories.id You would include product_categories.category_name in the SELECT list…, which it is currently missing.
I recommend that you use table alias names in your queries, e.g. b for brand, c for category, p for product, to simplify all the typing needed for a query and to always include them with each column reference, even in the cases where they are not required, so that your query becomes self-documenting.
I also wish people would quit, inconstantly, repeating parts of table names in the names of columns. You also have a product_brand column in the product table that is actually the brand name, and should be named ‘name’ and a product_brand column in the product table that is actually the brand id, and should be named ‘brand_id’.
Just about every select query should have an ORDER BY … term so that the rows in the result set will be in a specific, known order.
Lastly, to get the fetched data to be indexed/grouped by the category name when using PDO::FETCH_GROUP, the category name must be the first column in the SELECT … list.
Doing all these clean-up practices should result in -
// build sql queries in a php variable, to make debugging easier and help prevent typo mistakes in the syntax
$sql = "SELECT c.name, p.id, CONCAT(b.name,' ',p.name) b_p_name
FROM products p
INNER JOIN brands b ON p.brand_id = b.id
INNER JOIN product_categories c ON p.category_id = c.id
WHERE p.product_stock=? AND b.brand_active=?
ORDER BY c.name, b.name, p.name";
// only the final variable name holding the data needs to be specific
// this example names the variable holding the database connection as to the type of connection it is
$stmt = $pdo->prepare($sql);
$stmt->execute([1,1]);
// index/pivot the data by the first column selected - category name
$all_products = $stmt->fetchAll(PDO::FETCH_GROUP);
// build the output
$select_optin = '';
foreach($all_products as $category_name=>$arr)
{
// use single-quotes inside php double-quoted strings instead escaped double-quotes
$select_optin .= "<optgroup label='$category_name'>\n";
foreach($arr as $row)
{
$select_optin .= "<option value='{$row['id']}'>{$row['b_p_name']}</option>\n";
}
$select_optin .= "</optgroup>\n";
}
