[SOLVED] Error using AND with multiple OR statements MySQL Query

[jaxdevil]

Below is my query string, it doesn't seem to work properly. As it is now the column `status` in my table is blank, and when I make the code with it only having AND `status`='$status' at the end of the string, or putting `status`='$status' AND at the beginning of the string, it still pulls up all records (thats with sending nothing for $searchstring, so thats just all wildcards, but the $status variable is set as UNPAID, and since none of them are marked unpaid it should return no results. So I assumed (I assume now that I assumed wrongly the first time, the mother all f ups) that it was looking for blank OR blank OR blank OR 'blank AND something' meaning it took the last statement of OR and combined just that one with the AND, or at the beggining of the code it just took the AND and combined it with the terms before the first or and looked for that one OR any of the other ones. So I tried to blank and blank OR blank and blank OR blank and blank but that is giving an error, so I am still doing this wrong. What are you supposed to do to locate records that definetly match one aspect along with several possible matches?

 

$result = mysql_query("SELECT count(*) FROM receipt_of_bill WHERE `status`='$status' AND `bill_code` LIKE '%$searchstring%' OR `status`='$status' AND `vendor_name` LIKE '%$searchstring%' OR `status`='$status' AND `date_of_bill` LIKE '%$searchstring%'  OR `status`='$status' AND `ref_number` LIKE '%$searchstring%' OR `status`='$status' AND `amount_due` LIKE '%$searchstring%' OR `status`='$status' AND `bill_due` LIKE '%$searchstring%' OR `status`='$status' AND `memo` LIKE '%$searchstring%'");

[premiso]

Basic math I guess, order of operations.

 

<?php
$result = mysql_query("SELECT count(*) FROM receipt_of_bill WHERE (`status`='$status' AND `bill_code` LIKE '%$searchstring%') OR (`status`='$status' AND `vendor_name` LIKE '%$searchstring%') OR (`status`='$status' AND `date_of_bill` LIKE '%$searchstring%')  OR (`status`='$status' AND `ref_number` LIKE '%$searchstring%') OR (`status`='$status' AND `amount_due` LIKE '%$searchstring%') OR (`status`='$status' AND `bill_due` LIKE '%$searchstring%') OR (`status`='$status' AND `memo` LIKE '%$searchstring%')");
?>

 

You have to surround each pair of AND statements inside parans or else this will ultimately always be true thus return all rows.

[.josh]

First off you may want to put some parenthesis around some of those things to prevent ambiguity.  2nd, if the column is empty and it's still returning results, you may want to echo out $status to see if it's got what you expect.  If it doesn't, then you may in essence be doing status=''

 

edit: premiso beat me to the ( )

[Mchl]

Basic math I guess, order of operations.

 

And now some more advanced maths

 

<?php
$result = mysql_query("SELECT count(*) FROM receipt_of_bill WHERE `status`='$status' AND (`bill_code` LIKE '%$searchstring%' OR `vendor_name` LIKE '%$searchstring%' OR `date_of_bill` LIKE '%$searchstring%' OR `ref_number` LIKE '%$searchstring%' OR  `amount_due` LIKE '%$searchstring%' OR `bill_due` LIKE '%$searchstring%' OR `memo` LIKE '%$searchstring%')");
?>

 

[jaxdevil]

Works perfecto now! Mucho gracias!

 

Thanks!

SK