php - mysql 连接两个表

Question

table user:
id_u*   f_name   l_name
----------------------
1       andi     mitchel
2       sarah    bench
3       kirsty   larx

table voucher:
id_v*   id_user    id_target
1       1          2
2       2          3

很困惑如何用两个外键连接这些表

$db->query("SELECT * FROM voucher v 
LEFT JOIN user u ON u.id_u = v.id_user
LEFT JOIN user u1 ON u1.id_u = v.id_target
WHERE .... ")

回显 while 循环...并且不返回任何内容？

while($r = $q->fetch_array(MYSQLI_ASSOC)) :
   echo  $r['u.f_name'];
   echo  $r['u1.f_name'];
endwhile;

Answer 1

您的JOIN看起来绝对正确。唯一的问题是您已连接表 user 两次，因此您有具有相同名称的列(例如 f_name)。数据库将为这些列分配不同的(但任意的)名称。您可以使用 AS 关键字覆盖此行为:

$db->query("SELECT v.*
                 , u.f_name AS user_f_name
                 , u.l_name AS user_l_name
                 , ta.f_name AS target_f_name
                 , ta.l_name AS target_l_name
            FROM voucher v 
            LEFT JOIN user u ON u.id_u = v.id_user
            LEFT JOIN user ta ON ta.id_u = v.id_target
            WHERE .... ")

然后:

while($r = $q->fetch_array(MYSQLI_ASSOC)) :
   echo  $r['user_f_name'];
   echo  $r['target_f_name'];
endwhile;

我认为您可以用(内部)JOIN 替换LEFT JOIN。除非您的 id_user 或 id_target 值引用不存在的用户 ID (id_u)。