CODEWITHSUNDEEP
phpmysql
Suneth Kalhara
Suneth Kalhara

Reputation: 1208

PHP MySQL Search Query

I'm writing a code for MySQL search using two tables. But the results seems not correct

Interface Interface

"interpreters" Table Structure (This hold the info about interpreters)enter image description here

"terp_attributes" Table structure (This holds the attributes value ids available for terps) 

EX-  terp x has level 1 and level 2 certification 
Ex2- terp x can translate ASL and Spanish

enter image description here

Sample data on above table

enter image description here

I wrote a sql to get "Male or Female which has Certified (ta_attrvalid=1) or Level 1 Skills (ta_attrvalid=2)" and He also has "ASL" language (ta_attrvalid=6) attribute but it seems not working even i modified a lot. here is my query

SELECT * FROM 
interpreters ,terp_attributes 
WHERE (terp_gender='M' OR terp_gender='F') 
AND terp_agencyid=1 AND terp_id=ta_terpid 
AND ( ta_attrvalid=1 OR ta_attrvalid=2) 
AND ( ta_attrvalid=6)

Which should return "ta_terpid 3" data as the prediction but i cannot see any results

can anyone help me to resolve this query please, Thanks a lot

Upvotes: 0

Views: 184

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

It might be better to split your attributes table in interpreters_skills, interpreters_languages and interpreters_locations. However with your actual design you will need to join the table multiple times or use EXISTS subqueries like:

SELECT * 
FROM interpreters i
WHERE terp_gender IN ('M', 'F')
  AND terp_agencyid=1
  AND EXISTS (
      SELECT *
      FROM terp_attributes a
      WHERE a.ta_terp_id = i.terp_id
        AND ta_attrvalid IN (1, 2)
  )
  AND EXISTS (
      SELECT *
      FROM terp_attributes a
      WHERE a.ta_terp_id = i.terp_id
        AND ta_attrvalid IN (6)
  )

Upvotes: 1

Related Questions