CODEWITHSUNDEEP
phpmysqldatabase
alexander7567
alexander7567

Reputation: 664

SQL Query with 2 OR statments and 1 AND statement

I am attempting to run a SQL query like this:

$sql = mysql_query("select * from users where new_mail = 0 OR new_events = 0 and emailed = 1") or die(mysql_error());
while($r=mysql_fetch_array($sql))
{
    mysql_query("update users set emailed = 0 where username='{$r['username']}'") or die(mysql_error());
}

However it seem to ignore the OR statements and only follows the and statement. So it goes ahead and sets emailed to 0 even if they still have new_mail or new_event. Any clue why?

Thanks for your help!

Upvotes: 2

Views: 125

Answers (4)

Bad Wolf
Bad Wolf

Reputation: 8349

According to MYSQL order of operations, AND statements in a WHERE clause are evaluated before OR statements unless grouped. I'm assuming what you want to do is for the query to return true if either new_mail or new_events is 0 and emailed is set to 0.

If so you need to group your OR statement like so:

"SELECT * FROM users WHERE (new_mail = 0 OR new_events = 0) AND emailed = 1"

You can also simplify this to one query if you are not doing anything else in that loop by applying the WHERE clause directly to the UPDATE query like so.

$sql = "UPDATE users SET emailed = 0 WHERE (new_mail = 0 OR new_events = 0) AND emailed = 1"
mysql_query($sql) or die(mysql_error())

Upvotes: 2

trickyzter
trickyzter

Reputation: 1591

Put parenthesis around the OR clauses, segmenting it from the AND clause.

Upvotes: 2

dright
dright

Reputation: 653

$sql = mysql_query("select * from users where (new_mail = 0 OR new_events = 0) and emailed = 1") or die(mysql_error());
while($r=mysql_fetch_array($sql))
{
    mysql_query("update users set emailed = 0 where username='{$r['username']}'") or die(mysql_error());
}

You'll probably need to add parenthesis to the where clause

Upvotes: 2

newfurniturey
newfurniturey

Reputation: 38456

In MySQL, the AND operator has a higher precedence over the OR operator.

To get the logic you desire, try grouping the parameters in the way you want them to be evaluated:

$sql = mysql_query("select * from users where (new_mail = 0 OR new_events = 0) and emailed = 1") or die(mysql_error());

Upvotes: 7

Related Questions