user2905966 user2905966 - 1 month ago 14
MySQL Question

Left join and belongs to

I have three tables: products (id, name), categories (id, name) and products_categories (product_id, category_id).

Each product belongs to one or more categories.

I want to retrieve all products, and show which ones are already in category "X".
My divs are like this:

<span>Category "X"</span>
[some php / fetch_assoc() / … ]
<div class="product">Product A</div>
<div class="product selected">Product B</div>
<div class="product">Product B</div>


For now, it work with two queries: one to fetch all the products, and one to check if the product is in products_categories. So it's a lot of small queries with php inside the first one.

$getAllProducts = "SELECT products.name as productName, products.id as productID FROM products";
$resultProduct=$mysqli->query($getAllProducts);
while($product=$resultProduct->fetch_assoc()){
$reqChecked = "SELECT * FROM products_categories
WHERE product_id=" . $product["productID"] ."
AND category_id=" . $category["id"]; //$category["id"] is given
$resultChecked = $mysqli->query($reqChecked);
$row = $resultChecked->fetch_row();
$selected = ""
if ( isset($row[0]) ) {
$selected = "selected";
}


It is possible to do it with only one query? I tried with a left join (products_categories on products), but the products belonging to multiple categories are listed for every categories they're in.

Answer

This is just a simple join problem. I originally thought you might need some query magic to show whether a product belongs to a given category. But if you just use the query below, you can check the category name for each row in your PHP and act accordingly.

SELECT p.id,
       p.name AS product,
       c.name AS category       -- check for value 'X' in your PHP code
FROM products p
INNER JOIN products_categories pc
    ON p.id = pc.product_id
INNER JOIN categories c
    ON c.id = pc.category_id

Note that your current approach is actually trying to do the join in the PHP code itself, which is undesirable for so many reasons.