How to write a sql statement that combine 3 tables data?

Question

This is the code that i have came out after getting the data from 2 tables which are shipping infos and order. Among the data i get one of it is product_id. Which later the product_id is the primary key of the third table name product. From the third table product i would like to retrive product_title and product_price. How should i write my sql? Below shows the sql of retrieving data from 2 tables.

 <?php
 include ('classes/functions.php');

 if(isset($_POST['user_id'])){
$user_id = $_POST['user_id'];
$check_receipt = "select  si.shipping_name,
    si.shipping_address,
    si.shipping_contact,
    si.shipping_email,
    o.order_date,
    o.trx_id,
    o.tracking_num,
    o.quantity,
    o.product_id
   from shipping_infos si
   inner join orders o
on si.user_id = o.user_id    
where si.user_id='".$user_id."';";

        $run_receipt_checking = mysqli_query($con, $check_receipt);
        $result = array();
    while($row = mysqli_fetch_array($run_receipt_checking)){
    array_push($result,
    array(
          'shipping_name'=>$row[0],
          'shipping_address'=>$row[1],
          'shipping_contact'=>$row[2],
          'shipping_email'=>$row[3],
          'order_date'=>$row[4],
          'trx_id'=>$row[5],
          'tracking_num'=>$row[6],  
          'quantity'=>$row[7],
          'product_id'=>$row[8]       

));
}
echo json_encode(array("result"=>$result));
 }
?>

This is because table 1 and 2 share the same variable which is user_id while the 3rd table didnt have user_id instead it has product_id which table 2 also has. Can i combined the 3 tables with that? — Cheong Charlene, Apr 18 '16 at 09:33

score -1 · Answer 1 · answered Apr 18 '16 at 09:40

you can combine three tables with JOIN

Try like below

 <?php
 include ('classes/functions.php');

 if(isset($_POST['user_id'])){
$user_id = $_POST['user_id'];
$check_receipt = "select  si.shipping_name,
    si.shipping_address,
    si.shipping_contact,
    si.shipping_email,
    o.order_date,
    o.trx_id,
    o.tracking_num,
    o.quantity,
    o.product_id
   from shipping_infos si
   inner join orders o   
on si.user_id = o.user_id    
inner join products p on p.product_id  = o.product_id 
where si.user_id='".$user_id."';";

        $run_receipt_checking = mysqli_query($con, $check_receipt);
        $result = array();
    while($row = mysqli_fetch_array($run_receipt_checking)){
    array_push($result,
    array(
          'shipping_name'=>$row[0],
          'shipping_address'=>$row[1],
          'shipping_contact'=>$row[2],
          'shipping_email'=>$row[3],
          'order_date'=>$row[4],
          'trx_id'=>$row[5],
          'tracking_num'=>$row[6],  
          'quantity'=>$row[7],
          'product_id'=>$row[8]       

));
}
echo json_encode(array("result"=>$result));
 }

How to write a sql statement that combine 3 tables data?

1 Answers1