如何使用php和mysql从外部表中搜索数据?
这里是3 MySQL表格式:如何使用php和mysql从外部表中搜索数据?
project_status
psid p_id cdid sid short_list res_sent
1 3 112 24 0 0
2 3 113 25 0 1
3 4 114 22 0 1
4 4 115 23 1 0
5 5 116 26 0 0
contact_details:
cdid family_name given_name department title cid
112 xx xx xx xx 1
113 xx xx xx xx 2
114 xx xx xx xx 3
115 xx xx xx xx 4
116 xx xx xx xx 5
公司:
cid company_name
1 xx
2 xx
3 xx
4 xx
5 xx
现在,我需要从基于p_id
和sid
project_status
表中搜索cdid
显示family_name, given_name, department, title
所以我使用下面的查询,我得到3个结果
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*, company.company_name
FROM
contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE
1 = 1
AND ps.sid IN (25, 22)
AND ps.p_id = '3'
ORDER BY
company.company_name ASC
NOW我想再次在上搜索结果信息(有3个结果)基于family_name or given_name or department搜索字串
对于我使用下面的查询:
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*, company.company_name
FROM
contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE
1 = 1
AND ps.sid IN (25, 22)
AND company.company_name LIKE '%ab%'
OR cd.family_name LIKE '%ab%'
OR cd.given_name LIKE '%ab%'
AND ps.p_id = '3'
GROUP BY
cd.cdid
ORDER BY
company.company_name ASC
现在是不应该给我更多的3的结果,因为我有我的第一个查询3所示的结果。但不幸的是它向我显示contact_details table
尝试修改SQL的最后一部分:
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*,
company.company_name
FROM contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE ps.sid IN (25, 22)
AND (company.company_name LIKE '%ab%'
OR cd.family_name LIKE '%ab%'
OR cd.given_name LIKE '%ab%')
AND ps.p_id = '3'
GROUP BY cd.cdid
ORDER BY company.company_name ASC
让我现在试试。 –
所以我只需要添加'()'对吗? –
是的..它似乎工作:) –
你OR子句expaning您的标准超过3个结果远远超出了你认为
我觉得这可能是你换货什么来指定,它有点猜测虽然
的SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*, company.company_name
FROM
contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE ps.sid IN (25, 22)
AND company.company_name LIKE '%ab%'
AND (cd.family_name LIKE '%ab%' OR cd.given_name LIKE '%ab%')
AND ps.p_id = '3'
GROUP BY
cd.cdid
ORDER BY
company.company_name ASC
的1=1
是完全没有必要的也是
SELECT cd.family_name, cd.given_name, cd.department, cd.title, cd.cid, ps.*, company.company_name
FROM contact_details AS cd
INNER JOIN project_status AS ps ON cd.cdid = ps.cdid
INNER JOIN company ON company.cid = cd.cid
WHERE ps.sid IN (25,22) AND ps.p_id = '3'
AND (company.company_name LIKE '%ab%' OR cd.family_name LIKE '%ab%' OR cd.given_name LIKE '%ab%')
ORDER BY company.company_name ASC
为什么'WHERE 1 = 1'是什么增加了党 – RiggsFolly
包装你'或'子句'()'否则只会造成混乱目前大公正在扩大搜索条件超出你的想法 – RiggsFolly
@Avishek实际上不相关 – RiggsFolly