shibbir ahmed shibbir ahmed - 4 months ago 10
MySQL Question

How to search data from foreign table using php and mysql?

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

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
Comments