php - 如何在php mysql中显示商店名称而不是分支名称

Question

我有一个名为“tbl_stores”和“tbl_deals”的mysql表。基本结构是这样的——

我想在同一张表中通过商店 id 和分支 id 显示商店名称而不是分支名称

我尝试了以下代码，我得到了商店和分支名称

$d_id=$_REQUEST['id'];
$s_id=$_REQUEST['sid'];
$sql1 = "SELECT  t1.s_id,t1.p_id,t1.store_name,t1.store_address,t1.store_city,t1.store_lat,t1.store_lng,t1.store_cat,t1.store_status,t2.deal_name,t2.deal_desc,t2.deal_end,t2.deal_type,t2.deal_m,t2.deal_n,t2.deal_op,t2.deal_dp,t2.deal_pf,t2.deal_pa,t2.deal_sa,t2.deal_fi, t2.deal_images, t2.deal_stores,t2.deal_status 
FROM tbl_stores t1, tbl_deals t2 
WHERE t1.s_id=$s_id and t2.d_id=$d_id";

标签： phpmysqlmysqli

Answer 1

您需要加入tbl_stores自己才能从分支机构获取相关商店。

$sql1 = "SELECT  t1.s_id,t1.p_id,t3.store_name,t1.store_address,t1.store_city,t1.store_lat,t1.store_lng,t1.store_cat,t1.store_status,t2.deal_name,t2.deal_desc,t2.deal_end,t2.deal_type,t2.deal_m,t2.deal_n,t2.deal_op,t2.deal_dp,t2.deal_pf,t2.deal_pa,t2.deal_sa,t2.deal_fi, t2.deal_images, t2.deal_stores,t2.deal_status 
FROM tbl_stores t1
CROSS JOIN tbl_deals t2 
JOIN tbl_stores t3 ON t1.p_id = t3.s_id
WHERE t1.s_id=$s_id and t2.d_id=$d_id";

如果$s_id可以是主店而不是分店，则可以使用LEFT JOINandIFNULL()来处理这种情况。

$sql1 = "SELECT  t1.s_id,t1.p_id,IFNULL(t3.store_name, t1.store_name) AS store_name,t1.store_address,t1.store_city,t1.store_lat,t1.store_lng,t1.store_cat,t1.store_status,t2.deal_name,t2.deal_desc,t2.deal_end,t2.deal_type,t2.deal_m,t2.deal_n,t2.deal_op,t2.deal_dp,t2.deal_pf,t2.deal_pa,t2.deal_sa,t2.deal_fi, t2.deal_images, t2.deal_stores,t2.deal_status 
FROM tbl_stores t1
CROSS JOIN tbl_deals t2 
LEFT JOIN tbl_stores t3 ON t1.p_id = t3.s_id
WHERE t1.s_id=$s_id and t2.d_id=$d_id";