MySQL Advanced Query multiple AND and OR

Question

I'm drawing a blank this late at night after programming all day and maybe someone else can help shed light on where my issue lies. I'm trying to make a MySQL query in PHP where it gets the correct fields based on multiple variables using AND and OR.

Here is what I'm trying to do that is not working:

$result = mysql_query("SELECT * FROM tableName 
      WHERE key = '$key' ((userFrom = '$userFrom' AND userTo = '$userTo') 
                       OR (userFrom = '$userTo' AND userTo = '$userFrom')");

Basically in English what this does is it needs to know the Key value. Once it matches the key value then I need all the rows with that key value that are ALSO - FROM or TO the same users - think of it as a conversation between two people it has to have their conversation topic (key) and the two participants (from and to).

Any suggestions?

You're missing an AND between key = '$key' and the following parenthesis – is this a typo in your example? — millimoose
– millimoose, Commented Nov 3, 2011 at 2:58
Yeah I accidentally forgot to type that. I do have the AND in there in fact and still not working unfortunately. — Brayden
– Brayden, Commented Nov 3, 2011 at 3:02

Larry Lustig · Accepted Answer · 2011-11-03 03:05:30Z

2

Looks like three open parens and two closing parens to me. The missing one is immediately before the closing double quote. (Plus, of course, the missing AND mentioned by others).

Also, of course, various characters embedded in user names (if allowed) will make the command fail in data-specific ways. For that reason, and to avoid SQL Injection attacks, please switch to parameterized queries.

answered Nov 3, 2011 at 3:05

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

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

1 Comment

Brayden Over a year ago

You're correct. I re-read this another time, evaluated the parameters being POST to my page and realized there was an issue with it.

random · Accepted Answer · 2011-11-25 04:27:24Z

0

I think it's just a question of getting the parenthesis the correct way. Try this:

$result = mysql_query("SELECT * FROM tableName 
                       WHERE key = '$key' **AND** 
                       ((userFrom = '$userFrom' AND userTo = '$userTo') OR 
                        (userFrom = '$userTo' AND userTo = '$userFrom')");

Or perhaps:

$result = mysql_query("SELECT * FROM tableName 
                        WHERE ((key = '$key') AND 
                        ((userFrom = '$userFrom' AND userTo = '$userTo') OR 
                         (userFrom = '$userTo' AND userTo = '$userFrom'))");

I think if you fiddle with those you will get what you want.

edited Nov 25, 2011 at 4:27

random

9,97810 gold badges71 silver badges85 bronze badges

answered Nov 3, 2011 at 2:58

Johnny Mnemonic

1871 gold badge2 silver badges8 bronze badges

4 Comments

Brayden Over a year ago

Didn't seem to do the trick. I've been fiddling around with the positioning of the parenthesis and haven't had much luck strike me quite yet.

Johnny Mnemonic Over a year ago

I ran a query with a similar structure (with the extra closing parenthesis that Larry mentioned was missing) and it went off without a hitch.

Brayden Over a year ago

Do you have the exact query? I tried that and didn't work.. Here is my updated query: $result = mysql_query("SELECT * FROM chatloudChats WHERE ((apiKey = '$apiKey') AND ((userFrom = '$userFrom' AND userTo = '$userTo') OR (userFrom = '$userTo' AND userTo = '$userFrom')))");

Larry Lustig Over a year ago

@Brayden, don't show us the php, show us the constructed SQL statement including the data substitution. Because you are generating dynamic SQL, the contents of the variables matters (see my answer).

Collectives™ on Stack Overflow

MySQL Advanced Query multiple AND and OR

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related