CODEWITHSUNDEEP
phpmysql
IamGabros
IamGabros

Reputation: 43

PHP MySQL data retrieve from 3 tables

There is 3 tables:

First table (oc_product_to_category):

enter image description here

Second table (oc_product_description):

enter image description here

Third table (oc_product):

enter image description here

I want to list product name, image, price where category_id is 59 to this div:

<div class="content" style="">
                <h1>'Product image'</h1>
                <h3>Product name and price</h3>
</div>

I am beginner in working with multiple tables. Can somebody help me?

<?php 
    $servername = "localhost";
    $username = ""; 
    $password = ""; 
    $dbname = ""; 
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) { 
        die("Connection failed: " . $conn->connect_error);
    } 
    $sql = "SELECT product_id FROM oc_product_to_category WHERE category_id='62'";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) { // output data of each row
        while($row = $result->fetch_assoc()) { 
            echo "<div class='content' style=''> <h1>Orbit does content now.</h1> <h3>" . $row["product_id"]. "</h3> </div>";
    } } 
    $conn->close(); 
 ?>

UPDATE: price in: oc_product table

Upvotes: 0

Views: 56

Answers (1)

EllisTheEllice
EllisTheEllice

Reputation: 915

In my opinion this is more a question related to SQL than to PHP. In each table, you have a product_id column, so you can use this as a foreign key. The SQL should look similar to:

SELECT 
    pd.name, p.image 
FROM 
    oc_product_to_category ptc 
JOIN 
    oc_product_description pd ON ptc.product_id=pd.product_id 
JOIN 
    oc_product p ON ptc.product_id=p.product_id
WHERE
    ptc.category_id=59;

If you make this query using php, you get an array containing name and image of each row matching the given category_id.

PS: I did not find any price column.

Upvotes: 1

Related Questions