Snappysites Snappysites - 4 years ago 100
SQL Question

MySQLi != not working on multiple AND/OR clauses

So I have two rows in my Database -

user_one
and
user_two
, these rows are occupied by User ID's as numbers for a messaging system, I have also included a support ticket system within the same message feature but am having a few issues with this code snippet...

$sqlc="SELECT * FROM ap_conversations WHERE user_one = '$user_id' AND user_two != '999' OR user_two = '$user_id' AND user_one != '999' OR user_one = '$user_id' AND user_two != '999d' OR user_two = '$user_id' AND user_one != '999d'";


As you'll notice, the support tickets ID is
999
, and when it was kept to just checking that neither ID's were
999
this code worked perfectly. Although I also need it to check that neither
user_one
or
user_two
has ID's of
999
or
999d
. This current code returns
1
, when I know for a fact it should return
0
so I know something is defiantly going wrong and I think I may have it set up incorrectly. I have attempted to set it up like this in many different formats:

$sqlc="SELECT * FROM ap_conversations WHERE user_one = '$user_id' AND (user_two != '999' OR user_two != '999d') OR user_two = '$user_id' AND (user_one != '999' OR user_one != '999d')";


But still get the same results? Can anybody tell me what I am doing wrong here or how I can make this code perform better?

Answer Source
   (user_one = '$user_id' AND user_two != '999')
OR (user_one = '$user_id' AND user_two != '999d') 

...is a problem, it simplifies to user_one = '$user_id', the AND conditions become irrelevant.

If you don't believe me, try a row where user_one = user_id and user_two = '999'.. It fails the first bracketed condition, but passes the second and is returned.

I assume you want:

SELECT * FROM ap_conversations 
 WHERE (user_one = :user_id AND user_two NOT IN ('999','999d')) 
    OR (user_two = :user_id AND user_one NOT IN ('999','999d'));

N.B. Passing in '999' or '999d' as the $user_id will still return the support tickets. I quite like this functionality but if this is not desired you could use:

SELECT * FROM ap_conversations 
 WHERE (user_one = :user_id OR user_two = :user_id) 
   AND user_one NOT IN ('999','999d')
   AND user_two NOT IN ('999','999d');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download