我在mysql中尝试了多个LEFT JOIN,它在那里工作得很好,但是如果在php mysqli对象方法中使用相同的查询,我没有得到我在直接mysql
中得到的东西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结果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
与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);
}
我只是var_dump
$row
看结果。当我看到我没有得到id
为第6行mysql上看到的,而不是我得到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
我期望从水果表(这是1,水果名称是芒果)中获得id,但它返回null
。
不知道为什么会这样。任何帮助吗?fruit_stock
表
id f_id stock_qty
1 3 122
2 2 322
3 4 23
4 5 12
orders
表
id fruit_id qty
1 2 3
2 3 10
3 4 15
4 3 8
我不想关心其他表中的id,我只想要主(水果)表id和其他表中相应的整个数据
您可以为您的id添加别名或删除无用的(可选的注释):
$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';
那么你的数组中将只有一个索引id
您只能拥有一个值为Id
的键,因此当查询运行时,它首先是1,然后当它击中第二个Id
时,它会用null
覆盖它,请注意,您在数组中只有1个Id,但在查询中有两个。您需要在查询中更有选择性一些,并对一些列进行别名。