SELECT * FROM .. WHERE ??

[Paradoxz]

This is probably something simple I am missing, the below code works:

 

$result = mysql_query("SELECT * FROM $tableid WHERE `THIS`.`red` = 1 AND `THIS`.`white` = 1 AND `THIS`.`blue` = 1 AND `THIS`.`orange` = 0 or `THIS`.`orange` = 1 ");

 

So red, white and blue must = 1 but orange can = 0 or 1

 

Here is my problem, if I do that same code backwards to where

 

red, white, or blue = 0 or 1 but orange must = 1 then it still pulls up

one of the rows that have 0 for THIS.orange but not all (weird right?)

 

$result = mysql_query("SELECT * FROM $tableid WHERE `THIS`.`red` = 0 or `THIS`.`red` = 1 AND `THIS`.`white` = 0 or `THIS`.`white` = 1 AND `THIS`.`blue` = 0 or `THIS`.`blue` = 1 AND `THIS`.`orange` = 1 ");

 

Is the code that should pull up ***1 but is pulling up a one row that has 0 for orange and one row that has 1 for orange, in my database I have 3 rows

and under orange there are two with 0 and one with 1 so why is it only showing one 0 and one 1? it makes no sense! lol help me.

 

 

Also, I am using OR instead of >=0 because everything after WHERE = is dynamically generated so I can't change the = sign.

 

[.josh]

order of operations.  Need to wrap things with parenthesis, just like with math.

[Paradoxz]

Thanks for the quick response, I tried that and am missing something I guess, because what I tried was

 

$result = mysql_query("SELECT * FROM $tableid WHERE (`THIS`.`red` = 0 or `THIS`.`red` = 1) AND (`THIS`.`white` = 0 or `THIS`.`white` = 1) AND (`THIS`.`blue` = 0 or `THIS`.`blue` = 1) AND (`THIS`.`orange` = 1) ");

and that was a failure, I am not good with the parenthesis, does it need to just be a ( after before each one and then )))) at the end?

[Paradoxz]

You know, I don't understand why order of operations is neccessary, since it's not math it's just saying to query the database where ***1 or 111* or what not, just switches, on or off. But your definately a pro not me so i'm trying your idea, I have tried

 

$result = mysql_query("SELECT * FROM $tableid WHERE (`THIS`.`red` = 0 or `THIS`.`red` = 1) AND (`THIS`.`white` = 0 or `THIS`.`white` = 1) AND (`THIS`.`blue` = 0 or `THIS`.`blue` = 1) AND (`THIS`.`orange` = 1) ");

 

 

and

 

 

$result = mysql_query("SELECT * FROM $tableid WHERE (`THIS`.`red` = 0 or `THIS`.`red` = 1 AND (`THIS`.`white` = 0 or `THIS`.`white` = 1 AND (`THIS`.`blue` = 0 or `THIS`.`blue` = 1 AND (`THIS`.`orange` = 1 ")))));

 

And multiple other variations with no luck. If only I knew algebra or even basic math for that matter.  :-\

[.josh]

Well I have no idea what you're trying to accomplish with your query, so I can't really tell you where to put the parenthesis.  But as far as order of operations:  AND and OR are operators, just like +,-,* and /  they are logical operators.  So they have an order of precedence, just like math operators. 

[Paradoxz]

(sorry for the delay in this, took me 20 min to write this, work got busy)

 

I apologize, your right I didn't explain that very good. So basically I am trying to build a database for a tool store of a friends, I have all the tools on a database, and want the customers to be able to sort by some unique properties,

 

(i.e.:

id: 1 hammerp - duralast: 1 rubber: 1 highquality: 1

id: 2 hammerb  - duralast: 1 rubber: 1 highquality: 0

id: 3 hammerj  - duralast: 1 rubber: 0 highquality: 0

)

 

so I made a script that everything after WHERE = is dynamic,

if I want them to only retreive high quality hammers then I want to do

 

`THIS`.`duralast` = 0 or `THIS`.`duralast` = 1 AND

`THIS`.`rubber` = 0 or `THIS`.`rubber` = 1 AND

`THIS`.`highquality` = 1

 

my original plan was wildcards, but that did not work

 

`THIS`.`duralast` = %

`THIS`.`rubber` = %

`THIS`.`highquality` = 1

 

so wildcards basically since the only two options are 0 or 1 for the

first two variables but variable 3(high quality) has to be 1

 

%%1 or %%% or 11% or %11, etc.. any combination.

 

 

[Paradoxz]

Well, still no luck at this. So I guess time to start from the beginning. Just as a general question, if someone was to do a mysql query and wanted to be able to search a database for multiple things how would you do that? so I have dogs, cats, frogs, mice, chimps, etc. some are black, white, green, purple and some are tame or not tame.

If this doesn't work:

mysql_query("SELECT * FROM $tableid WHERE `THIS`.`breed` = 'cat' or `THIS`.`breed`= 'dog' AND `THIS`.`color` = 'Red' AND `THIS`.`agg` = 'tame'

 

Then how would you search for multiple specific properties at the same time as saying let one of them be a wildcard. if I want to search breed=% so i pull up all Red and Tame animals then how?