MySQL php join 4 tables and match 2 of them on another column

Question

I have 4 tables

product, product_description, product_to_category, category_description

Here are what my tables have as columns

Product

| product_id | model | quantity

---

product_description

| product_id | language_id | name | description

---

product_to_category

| product_id | category_id |

---

category_description

| category_id | language_id | name | description |

---

Can I join product_to_category with category_description on category_id and how?

---

Here is my code so far and it works , I have joined 3 tables on their product_id but I can't join category_description because it doesn't have a product_id

The idea behind my code is to get the name row from category_description

---

<?php
    include '../config.php';
    $servername = "localhost";
    $username = DB_USERNAME;
    $password = DB_PASSWORD;
    $database = DB_DATABASE;

    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    // Category Connection


    //Product connection
    $sql = "SELECT * FROM product,product_description,product_to_category 
            WHERE product.product_id = product_description.product_id 
            AND product.product_id = product_to_category.product_id
            AND product_description.language_id = 2";

    $conn->set_charset('utf8mb4');
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while ($product = $result->fetch_assoc()) {
            $product_name           = $product["name"];
            $product_category       = $product["category_id"];
            $product_id             = $product['product_id'];
            $product_description    = $product["description"];
            $product_model          = $product["model"];
            $product_price          = $product["price"];
            $product_image          = $product["image"];
            $product_qty            = $product["quantity"];

            echo $product_name . "<br>";
    }
}

/*Second connection*/
?>

Answer 1

Yes you can, I've also used explicit joins instead of your implicit joins. Because I think this is best practice.

SELECT * FROM product p
INNER JOIN product_description pd ON pd.product_id = p.product_id
INNER JOIN product_to_category ptc ON ptc.product_id = p.product_id
INNER JOIN category_description cd ON cd.category_id = ptc.category_id
WHERE pd.language_id = 2

Answer 2

To join all 4 tables, below is the query:

SELECT * FROM product p
JOIN product_description pd ON p.product_id = pd.product_id
JOIN product_to_category pc ON p.product_id = pc.product_id
JOIN category_description cd ON pc.category_id = cd.category_id
WHERE pd.language_id = 2