MySQL AND statement fails

Question

I have a query that fails It seems to be the AND statement that is failing.

if(isset($_POST['sea']) && $_POST['sm'] !==''){
$sm = trim($_POST['sm']);
$sql = "SELECT * FROM `memorials` WHERE `fname` LIKE '$sm%' OR `lname` LIKE '$sm%' OR    `comments` LIKE '$sm%' AND `status` = '2' ";
$q = $conn->prepare($sql);
$q->execute();

I have added\removed backticks, the 'status = '2' fails.

The following statement works in phpmyadmin

SELECT * FROM `memorials` WHERE `fname` LIKE 'test%' AND status = '2'

This statement fails in phpmyadmin

SELECT * FROM `memorials` WHERE `fname` LIKE 'test%' OR `lname` LIKE 'test%' AND status = '2'

This query searchs comment AND clause works, but ignore fname and lname

$sql = "SELECT * FROM memorials WHERE `fname`  OR  `lname`  OR `comments`  LIKE '$sm%'  AND status = '2'    ";

Does anyone have a suggestion where this is (I am) failing

Thank you

Gary

score 1 · Answer 1 · answered Feb 27 '13 at 20:19

1

You need to include parentheses around the appropriate section. Perhaps something like this:

SELECT * 
FROM `memorials` 
WHERE (`fname` LIKE 'test%' OR `lname` LIKE 'test%') AND status = '2'

answered Feb 27 '13 at 20:19

sgeddes

62,311
6
61
83

Thank you for your response, looks like your answer and Aarolama's are the same and it worked perfectly. – Gary Feb 27 '13 at 22:44
@Gary -- np, glad we could help! – sgeddes Feb 27 '13 at 23:07

score 1 · Answer 2 · answered Feb 27 '13 at 20:20

1

Operator precedence matters here. You need to add some parentheses:

SELECT * 
FROM   `memorials` 
WHERE  ( `fname` LIKE '$sm%' 
          OR `lname` LIKE '$sm%' 
          OR `comments` LIKE '$sm%' ) 
       AND `status` = '2'

Documentation.

answered Feb 27 '13 at 20:20

Kermit

33,827
13
85
121

Sorry for the delay (server went down), worked like a charm... Thank you for your help. – Gary Feb 27 '13 at 22:42
bluefeet - I had the question half written and tried another version and thought the more information the better. – Gary Feb 27 '13 at 22:46

MySQL AND statement fails

2 Answers2