CODEWITHSUNDEEP
phpmysql
112233
112233

Reputation: 2466

query doesn't filter results correctly

This is my query:

$sql = "SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('$lat' - lat) *  pi()/180 / 2), 2) +COS('$lat' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('$lon' - lon) * pi()/180 / 2), 2) ))) as distance  from posts WHERE posts.subname LIKE '%$subject%' AND posts.pricing <= '$rate' ".$add_here." GROUP BY posts.UUID having  distance <= '$distance' order by distance";

In the browser,

SELECT * , 
(3956 * 2 * ASIN(SQRT( POWER(SIN(('2.993518' - lat) * pi()/180 / 2), 2) + 
COS('2.993518' * pi()/180) * COS(lat * pi()/180)
 * POWER(SIN(('101.7874058' - lon) * pi()/180 / 2), 2) ))) 
    as distance
from posts 
WHERE posts.subname LIKE '%tamil%' 
AND posts.pricing <= '161' 
AND posts.morning IN ('sun') 
OR posts.afternoon IN ('sun') OR posts.evening IN ('sun','mon') 
GROUP BY posts.UUID 
having distance <= '40' 
order by distance

The problem I'm having is, the result for this query doesn't filter for the subname. It simply filter for the AND posts.pricing <= '161' AND posts.morning IN ('sun') OR posts.afternoon IN ('sun') OR posts.evening IN ('sun','mon') .

As long as the morning, afternoon and evening fields matched result returned without filtering for subname LIKE %tamil%.

I want the query to search through morning, afternoon and evening fields for rows that matches subname LIKE %tamil%. How do I alter this way, please?

Upvotes: 0

Views: 32

Answers (1)

ChrisGuest
ChrisGuest

Reputation: 3608

The problem is in your WHERE clause.

WHERE posts.subname LIKE '%tamil%' 
AND posts.pricing <= '161' 
AND posts.morning IN ('sun') 
OR posts.afternoon IN ('sun') OR posts.evening IN ('sun','mon')

If I understand you, you are trying to say select for: '%tamil%' AND pricing<161 AND (morning or afternoon or evening)

Because you have omitted the parenthesis the clause is true for afternoon OR evening.

I believe that you want to do this:

WHERE posts.subname LIKE '%tamil%' 
AND posts.pricing <= '161' 
AND (posts.morning IN ('sun') 
    OR posts.afternoon IN ('sun') 
    OR posts.evening IN ('sun','mon'))

Upvotes: 1

Related Questions