PHP MySQL Search Query

Question

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

Interface

"interpreters" Table Structure (This hold the info about interpreters)

"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

Sample data on above table

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

I believe, you need to specify the table name in your WHERE clause. SELECT * FROM interpreters ,terp_attributes WHERE (interpreters.terp_gender='M' OR interpreters.terp_gender='F') AND interpreters.terp_agencyid=1 AND terp_attributes.terp_id=ta_terpid AND ( terp_attributes.ta_attrvalid=1 OR terp_attributes.ta_attrvalid=2) AND ( terp_attributes.ta_attrvalid=6) — cosmoonot
– cosmoonot, Commented Apr 9, 2017 at 17:25
AND ( ta_attrvalid=1 OR ta_attrvalid=2) AND ( ta_attrvalid=6) - This is not possible. — Paul Spiegel
– Paul Spiegel, Commented Apr 9, 2017 at 17:28
@PaulSpiegel is there any other way to do this or shall i keep those attributes relationships in 3 separate tables, Thank You — Suneth Kalhara
– Suneth Kalhara, Commented Apr 9, 2017 at 17:30
Create same sample data and expected result. It's not clear, what you want. I guess you need some EXISTS subqueries. — Paul Spiegel
– Paul Spiegel, Commented Apr 9, 2017 at 17:39
"ta_terpid 3" literally match with this "Certified (ta_attrvalid=1) or Level 1 Skills (ta_attrvalid=2)" and has "ASL" language (ta_attrvalid=6) " but i don't know how to create a query for that — Suneth Kalhara
– Suneth Kalhara, Commented Apr 9, 2017 at 17:43

Paul Spiegel · Accepted Answer · 2017-04-09 17:52:35Z

1

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)
  )

answered Apr 9, 2017 at 17:52

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suneth Kalhara Over a year ago

Thanks a lot,I'll check this

Suneth Kalhara Over a year ago

It works great, Thanks a lot for help :) Good luck...!

Collectives™ on Stack Overflow

PHP MySQL Search Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related