rram rram - 2 months ago 9
MySQL Question

Multiple LEFT Join works correct in mysql but not in php

I tried mutiple LEFT JOIN in mysql it works fine there but however if use the same query in php mysqli object method I am not getting what I get in direct mysql

HERE is the Query in MYSQL

SELECT f.*,o.*,fs.* FROM fruits f
LEFT JOIN orders o ON f.id = o.fruit_id
LEFT JOIN fruit_stock fs ON f.id = fs.f_id


MYSQL RESULT

id name price id fruit_id qty id f_id stock_qty
3 Banana 5 2 3 10 1 3 122
3 Banana 5 4 3 8 1 3 122
2 Apple 3 1 2 3 2 2 322
4 pomegranate 4 3 4 15 3 4 23
5 grape 3 NULLNULL NULL 4 5 12
1 mango 45 NULLNULL NULL NULLNULL NULL


Same query with php

$con1 = new mysqli('***','***','***','***');
$sel_sql = 'SELECT f.*,o.*,fs.* FROM fruits f LEFT JOIN orders o ON f.id = o.fruit_id LEFT JOIN fruit_stock fs ON f.id = fs.f_id';
$result = $con1->query($sel_sql);
var_dump($result);
while($row = $result->fetch_assoc()){
var_dump($row);
}


I just
var_dump
the
$row
to see the result. When I see I am not getting
id
for the 6th row as seen on mysql instead I get
null


array (size=7)
'id' => string '1' (length=1)
'name' => string 'Banana' (length=6)
'price' => string '5' (length=1)
'fruit_id' => string '3' (length=1)
'qty' => string '10' (length=2)
'f_id' => string '3' (length=1)
'stock_qty' => string '122' (length=3)

array (size=7)
'id' => string '1' (length=1)
'name' => string 'Banana' (length=6)
'price' => string '5' (length=1)
'fruit_id' => string '3' (length=1)
'qty' => string '8' (length=1)
'f_id' => string '3' (length=1)
'stock_qty' => string '122' (length=3)

array (size=7)
'id' => string '2' (length=1)
'name' => string 'Apple' (length=5)
'price' => string '3' (length=1)
'fruit_id' => string '2' (length=1)
'qty' => string '3' (length=1)
'f_id' => string '2' (length=1)
'stock_qty' => string '322' (length=3)

array (size=7)
'id' => string '3' (length=1)
'name' => string 'pomegranate' (length=11)
'price' => string '4' (length=1)
'fruit_id' => string '4' (length=1)
'qty' => string '15' (length=2)
'f_id' => string '4' (length=1)
'stock_qty' => string '23' (length=2)

array (size=7)
'id' => string '4' (length=1)
'name' => string 'grape' (length=5)
'price' => string '3' (length=1)
'fruit_id' => null
'qty' => null
'f_id' => string '5' (length=1)
'stock_qty' => string '12' (length=2)

array (size=7)
'id' => null
'name' => string 'mango' (length=4)
'price' => string '45' (length=2)
'fruit_id' => null
'qty' => null
'f_id' => null
'stock_qty' => null


I am expecting to get the id from the fruit table(which is 1 and the fruit name is mango) but its returning
null
.

Not sure why is that happening. Any help?
fruit_stock
TABLE

id f_id stock_qty
1 3 122
2 2 322
3 4 23
4 5 12


orders
Table

id fruit_id qty
1 2 3
2 3 10
3 4 15
4 3 8


I don't want to care about id in other table I just want the main(fruits) table id and other corresponding whole data from other tables.

Answer

You can add alias to your ids or remove the useless ones (optional ones are commented):

$sel_sql = 'SELECT 
    f.*,
    -- o.id as order_id, 
    o.fruit_id,
    o.qty,
    -- fs.id as fruit_stock_id,
    fs.f_id,
    fs.stock_id
 FROM fruits f LEFT JOIN orders o ON f.id = o.fruit_id LEFT JOIN fruit_stock fs ON f.id = fs.f_id';

Then you will have only one index id on your array.