Check conditions in WHERE clause

[thara]

I am trying to run a query something like this:

from WHERE clause :

 

WHERE ( s.subject_name LIKE '%$keyword%' OR
c.city_name LIKE '%$keyword%' OR
o.option_name LIKE '%$option%' OR
d.district_id = $districtId OR
c.city_id = $cityId
)

 

With this conditions, first two are compulsory ($keyword from subject_name or city_name) and other 3 are optional. The query not meet last three conditions result should comes with $keyword matching in subject_name or city_name. If $option have a value result should come with matching $keyword and $option. Further if $districtId have a value result should become from matching $keyword, $option and $districtId. Finally if $cityId have a value query should check all conditions to return records.

 

My problem is when I create WHERE clause like above in my query it is not meeting my criteria.

 

Can anybody help me to do this?

Thank you.

Edited February 19, 2013 by thara

[dodgeitorelse3]

wouldn't you use '% ".$keyword."% ' ?

[requinix]

With this conditions, first two are compulsory ($keyword from subject_name or city_name) and other 3 are optional.

No, you've ORed all of them together which means that at least one of the five has to match. There are no restrictions on which.

 

Don't use conditions that you don't want to check. If $option doesn't have a value then don't include it at all.

[jazzman1]

I'm totally agree with reguinix.

 

@thara, take a look for a moment at that table,

 

Intermediate result				 Final result

WHERE TRUE OR TRUE		 TRUE

WHERE FALSE OR FALSE	 FALSE

WHERE FALSE OR TRUE		 TRUE

WHERE TRUE OR FALSE		 TRUE

[thara]

No, you've ORed all of them together which means that at least one of the five has to match. There are no restrictions on which.

 

Don't use conditions that you don't want to check. If $option doesn't have a value then don't include it at all.

 

 

My conditions are...

• $keyword is always should true. Other 3 may true or not.
  
• Only $keyword have a value return records should come matching with $keyword insubject_name or city_name.
  
• If $optional have a value then return records should come matching with $keyword and $option
  
• If $districtId have a value then result should come matching in 3 variable values. ($keyword,$option, $districtId)
  
• Finally if $cityId have a value along with others return result should come with matching all 4 variables' values.
  

This conditions come from my search page. $keyword is text box value and its always need a keyword to start searching. That's why I said it is always true. Other 3 values come from select box that select the items when they needed.

 

I tried to make the query, its ok. but really confusing in WHERE clause. Above code is from my WHERE clause so far. But It can not get my desired result.

[P5system]

Your query should be like this:

WHERE s.subject_name LIKE '%$keyword%' AND

c.city_name LIKE '%$keyword%' OR

(o.option_name LIKE '%$option%' OR

d.district_id = $districtId OR

c.city_id = $cityId

)