Query to fetch multiple data by joining multiple tables using PHP and MySQL

Question

I need one help .I need to fetch some data after joining the multiple table using PHP and Mysql so i need the appropriate query for that.I am explaining my table structure below.

db_order:

id     order_id    promocode

1      10           A12016

2       11          A12016

db_order_product:

id   order_id   pro_data_id  quantity

1     10         20          2

2     10         22          3

3     11         20          1

db_product_info:

pro_data_id   product_name

20            abc

22            xyz

I have tried something like below but its not working.

$sqlqry="select * from db_order order by id desc";
$orderqry=mysqli_query($con,$sqlqry);
while($row=mysqli_fetch_assoc($orderqry)){
    $order_id=$row['order_id'];
    $sqlproqry="select * from db_order_products where order_id='".$order_id."'";
    $proqry=mysqli_query($con,$sqlproqry);
    while($row1=mysqli_fetch_assoc($proqry)){
        $product_data_id=$row1['pro_data_id'];
        $sqldataqry="select * from db_product_data where pro_data_id='".$product_data_id."'";
        $prodataqry=mysqli_query($con,$sqldataqry);
        while($prodatarow=mysqli_fetch_assoc($prodataqry)){
            $pro_id=$prodatarow['pro_Id'];
            $sqlpro="select * from db_product_info where pro_Id='".$pro_id."'";
            $prodata=mysqli_query($con,$sqlpro);
            $prorow=mysqli_fetch_array($prodata);
        }
    }
$result[]=array('id'=>$row['id'],'order_id'=>$row['order_id'],'promocode'=>$row['promocode'],'order_pro_id'=>$row1['id'],'pro_data_id'=>$row1['pro_data_id'],'pro_quantity'=>$row1['quantity'],'product_name'=>$prorow['Product_name']);
}
echo json_encode($result);

Here i need first user will go to db_order table fetch all value and according to the order_id the all data should fetch from db_order_product then as per pro_data_id from db_order_product table it will fetch data from db_product_info table and finaly return all data in an array.Please help me.

Where is your query? You're not expecting us to write it for you, are you? This sounds like a simple JOIN between the 3 tables, very basic SQL. Please show what you've tried, and explain the problem you're having with it so we can help you fix it. — Barmar
– Barmar, Commented Feb 4, 2016 at 6:43

Barmar · Accepted Answer · 2016-02-04 07:12:09Z

1

Just use a simple JOIN

SELECT o.id, o.order_id, o.promocode, p.id AS order_pro_id, p.prod_data_id, p.quantity AS pro_quantity, i.product_name
FROM db_order AS o
JOIN db_order_products AS p ON o.order_id = p.order_id
JOIN db_product_data AS d ON p.pro_data_id = d.pro_data_id
JOIN db_product_info AS i ON i.pro_Id = d.pro_Id
ORDER BY o.id DESC

edited Feb 4, 2016 at 7:12

answered Feb 4, 2016 at 6:52

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

satya Over a year ago

As you can see there is two order_id as 10 in db_order_product: table.Will it work accordingly.

Barmar Over a year ago

Yes. JOIN produces a cross-product of all matching rows.

satya Over a year ago

@ Barmar : as you can see the first two table db_order: and db_order_product: has auto increment column name(id) are same and i need both id.Can you tell how can i fetch both using different key.

Barmar Over a year ago

Now all the columns will be fetched with the names you use in your JSON.

user4486345 · Accepted Answer · 2016-02-04 06:50:20Z

0

you must fix you query:

use this query instead:

  select * from db_order,db_order_product,db_product_info where db_order.order_id=db_order_product.order_id and db_order_product.pro_data_id=db_product_info.pro_data_id

answered Feb 4, 2016 at 6:50

user4486345

Comments

Magicprog.fr · Accepted Answer · 2016-02-04 09:49:11Z

0

SELECT dor.id,dordor.order_id,dor.promocode
FROM db_order AS dor
JOIN db_order_product AS dop ON dop.order_id = dor.order_id
JOIN db_product_info as dpi ON dpi.pro_data_id=dop.pro_data_id

edited Feb 4, 2016 at 9:49

Magicprog.fr

4,1204 gold badges29 silver badges35 bronze badges

answered Feb 4, 2016 at 6:54

Hamidreza Bavafa

184 bronze badges

Collectives™ on Stack Overflow

Query to fetch multiple data by joining multiple tables using PHP and MySQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related