CODEWITHSUNDEEP
phpmysql
user2320500
user2320500

Reputation: 169

Using a MySQL WHERE clause

I have a query in which I am using a WHERE clause.

My QUERY:

SELECT * FROM notifications WHERE recipient= '".$_SESSION['id']."' AND viewed >  
'".$twoweeksago."' OR viewed IS NULL ORDER BY recieved DESC

Basically, the query selects all rows of data where the recipient is the user logged in and viewed is a date less than two weeks old ($twoweeksago is a variable set in my script). But, the default for the viewed column is 0000-00-00 00:00:00 so I had to use:

OR viewed IS NULL

This creates a problem. I thought it was a condition only applied to the previous condition, which is:

 AND viewed > '".$twoweeksago."'

But, it is actually an OR for the entire WHERE clause and voids all previous conditions. How can I write an OR condition that only affects AND viewed > '".$twoweeksago."'

Thanks!

Upvotes: 0

Views: 90

Answers (6)

Bioblaze Payne
Bioblaze Payne

Reputation: 452

SELECT *
FROM table
WHERE
receipt = session
HAVING
viewed > time OR
viewed = null
ORDER BY
sort DESC

Using Pdo Your Example would look like this.

$link = new PDO('mysql:host=localhost;dbname=db', 'User', 'Pass');
$entry = $link->prepare('SELECT * FROM table WHERE receipt = :session HAVING viewed > :time OR viewed = null ORDER BY received DESC');
$entry->execute(array('session' => $Session, 'time' => $Time));

Makes it alot easier to Read, not to mention. Now you have more controll less ability to mess up. :3 PDO for the win anyone?

Upvotes: 0

rfsbsb
rfsbsb

Reputation: 519

You should wrap your conditions in a parenthesis like this:

SELECT * FROM notifications WHERE recipient= '".$_SESSION['id']."' AND (viewed > '".$twoweeksago."' OR viewed IS NULL) ORDER BY recieved DESC

This should work.

As a side note, you should remember to not use variables directly from the user, since it can introduce a security breech.

Upvotes: 1

Ahmed Habib
Ahmed Habib

Reputation: 189

Try this one

SELECT * FROM notifications WHERE recipient= '".$_SESSION['id']."' AND (viewed >  
'".$twoweeksago."' OR viewed=NULL ORDER BY recieved DESC

Upvotes: 0

jterry
jterry

Reputation: 6269

SELECT * FROM notifications
WHERE recipient= '" . $_SESSION['id'] . "'
AND (
    viewed > '" . $twoweeksago . "'
    OR viewed IS NULL
)
ORDER BY recieved DESC;

Upvotes: 0

skrilled
skrilled

Reputation: 5371

SELECT * FROM notifications WHERE recipient= '".$_SESSION['id']."' AND (viewed IS NULL OR viewed > '".$twoweeksago."') ORDER BY recieved DESC

i believe that will work.

Upvotes: 0

Erik Schierboom
Erik Schierboom

Reputation: 16636

You should add parenthesis around the grouped clause:

SELECT * FROM notifications WHERE recipient= '".$_SESSION['id']."' AND (viewed >  '".$twoweeksago."' OR viewed IS NULL) ORDER BY recieved DESC

Upvotes: 0

Related Questions