LIKE with OR's returning too many results

rpupkin77

Hello,

I have a job table that as an FK to a states table which, of coourse, holds all the states.

The FK is state_id.

Essentially, when somebody searches, I use LIKE to compare the term to several different fields. The problem is that the query returns a result for every state. So, if teh result should return 3 jobs, it returns 153 (50 states + DC) * 3...

$q = "Select j.job_id, j.title, j.company, j.description, j.showStart_date, j.url, j.city, j.confirmCode, s.name FROM jobs j, states s WHERE j.tags LIKE '%$term%' OR j.description LIKE '%$term%' OR j.title LIKE '%$term%' AND j.state_id = s.state_id AND j.end_date > $now ORDER BY j.end_date ASC";

If I strip out the OR statements and only go with 1 LIKE statement, it works.

Any ideas?

Thanks in advance.

Roger_Ramjet

This is because of operator precedence OR is executed before AND so your ORs are matched before the state id is looked at.

You should be coding it like this

$q = "Select j.job_id, j.title, j.company, j.description, j.showStart_date, j.url, j.city, j.confirmCode, s.name 
FROM jobs j INNER JOIN states s USING(state_id) 
WHERE j.end_date > $now AND (j.tags LIKE '%$term%' OR j.description LIKE '%$term%' OR j.title LIKE '%$term%') 
ORDER BY j.end_date ASC";

Get your join right first, then let your logic right.

rpupkin77

ok, thanks, I actually had them first but moved them, the parens is what I was missing.

Thank You very much

bradgrafelman

Don't forget to mark this thread resolved.

Roger_Ramjet

The most important thing that was missing was the correct JOIN syntax, read the manual and learn to use joins properly. If you got that comma way of doing it from a book or tutorial then dump it, the book or tute is crap and teaching you the wrong way to write SQL.