user2905966 user2905966 - 11 months ago 60
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 as productName, as productID FROM products";
$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 Source

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, AS product, AS category       -- check for value 'X' in your PHP code
FROM products p
INNER JOIN products_categories pc
    ON = pc.product_id
INNER JOIN categories c
    ON = 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.