How to write SQL query with LIKE operator

Question

I am trying to write a SQL query to get the data from the database but it doesn't execute properly. Everything works properly until I add the part

AND adstatus='success'

There is no problem executing the query. The problem is adstatus equals to pending in database, but still it gives all the results when adstatus is set to success in the query with LIKE operators.

Here is my code:

<?php
$sql = "SELECT * FROM ad WHERE adtitle LIKE '%$keyword%' OR addescription LIKE '%$keyword%' OR adcontactemail LIKE '%$keyword%' AND adstatus='success'";

$result = mysqli_query($conn, $sql);
$queryResult = mysqli_num_rows($result);

if ($queryResult > 0) {
    echo 'Results found: '.$queryResult;
} else {
    echo 'No results matching your serach!';
}
?>

$queryResult values should be less than 0 since adstatus value in database is pending but still it prints:

Results found: 3

How to write SQL with both LIKE operator and a WHERE condition like above?

M.Hemant · Accepted Answer · 2019-05-01 11:09:14Z

4

Try this,

$sql = "SELECT * FROM ad WHERE adstatus='success'
 AND  ( adtitle LIKE '%$keyword%' OR addescription LIKE '%$keyword%' OR adcontactemail LIKE '%$keyword%' )";

The MySQL AND & OR conditions allow you to test multiple conditions.
Don't forget the order of operation parentheses!

answered May 1, 2019 at 11:09

M.Hemant

2,3551 gold badge11 silver badges14 bronze badges

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

Comments

Rakesh Jakhar · Accepted Answer · 2019-05-01 11:39:22Z

1

The OR condition should be inside the parentheses

SELECT * FROM ad WHERE 
adstatus='success' AND 
(adtitle LIKE '%$keyword%') OR 
(addescription LIKE '%$keyword%') OR 
(adcontactemail LIKE '%$keyword%')

answered May 1, 2019 at 11:39

Rakesh Jakhar

6,4082 gold badges13 silver badges20 bronze badges

Comments

Onurus · Accepted Answer · 2019-05-01 12:12:25Z

1

In your Query:

sql = "SELECT * FROM ad WHERE adtitle LIKE '%$keyword%' OR addescription LIKE '%$keyword%' OR adcontactemail LIKE '%$keyword%' AND adstatus='success'";

exp1 = { adtitle LIKE '%$keyword%' }
exp2 = { addescription LIKE '%$keyword%' }
exp3 = { adcontactemail LIKE '%$keyword%' }
exp4 = { adstatus='success' }

So, you have :

exp1 OR exp2 OR exp3 AND exp4

And has precedence over Or to see

That means, it will be executed like :

exp1 OR ( exp2 OR (exp3 AND exp4) )

To achieve your need, you have to write like :

exp4 AND (exp1 OR exp2 OR exp3)

answered May 1, 2019 at 12:12

Onurus

541 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to write SQL query with LIKE operator

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related