Php search filter showing all rows without filtering

Question

While displaying the filtered data, this sql can filter data as per $locationone and $locationtwo.

But it is failing to filter data as per $cate

I mean its displaying all the rows from both locations and failing to filter it as per science(topic)

$cat= "science";
$cate= preg_replace("#[^0-9a-z]#i","", $cat);
$locationone= "dhk";
$locationtwo= "ctg";
preg_replace("#[^0-9a-z]#i","", $locationone);
preg_replace("#[^0-9a-z]#i","", $locationtwo);
$sql= "SELECT * FROM post INNER JOIN user ON post.user_id= user.id
WHERE post.topic LIKE '%$cate%'
AND post.location LIKE '%$locationone%' 
OR post.location LIKE '%$locationtwo%'
order by post_id desc";

You should check https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and use prepared statements. Also run your query in phpmyadmin or workbench, to test them — nbk, Dec 08 '19 at 16:04

score 0 · Answer 1 · answered Dec 08 '19 at 16:02

Logical operator OR has lower prescedence than AND. So you need to surround the ORed conditions with parentheses:

WHERE 
    post.topic LIKE '%$cate%'
    AND (post.location LIKE '%$locationone%' OR post.location LIKE '%$locationtwo%')

Without the parentheses, your code is equivalent to:

WHERE 
    (post.topic LIKE '%$cate%' AND post.location LIKE '%$locationone%')
    OR post.location LIKE '%$locationtwo%'

Here you can see that this code will allow locations that match on 'locationtwo' whatever the value of topic.

Important note: you do want to use prepared statements and parameterized queries everywhere in your code, for security and efficiency. See this post for how to proceed.

Php search filter showing all rows without filtering

1 Answers1