如何使用php和mysql从外表中搜索数据？

Question

Here is 3 mysql tables format :

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

company :

cid   company_name
1     xx
2     xx
3     xx
4     xx
5     xx

Now, I need to search cdid from project_status table based on p_id and sid to show family_name, given_name, department, title

So that I am using following query and I get 3 results

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 again I want to search on last result data (which was 3 results) based on family_name or given_name or department search string

For that I am using following query :

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

Now it's should not show me more that 3 result because I have 3 result shown on my first query. BUT unfortunately it's showing me more than 3 results from contact_details table

Answer 1

Try to modify last part of 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