3

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?

finefoot
  • 9,914
  • 7
  • 59
  • 102
Jananath Banuka
  • 2,951
  • 8
  • 57
  • 105

3 Answers3

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!

M.Hemant
  • 2,345
  • 1
  • 9
  • 14
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%')
Rakesh Jakhar
  • 6,380
  • 2
  • 11
  • 20
1

In your Query:

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

  1. exp1 = { adtitle LIKE '%$keyword%' }
  2. exp2 = { addescription LIKE '%$keyword%' }
  3. exp3 = { adcontactemail LIKE '%$keyword%' }
  4. 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)
Onurus
  • 54
  • 1
  • 4