CODEWITHSUNDEEP
phpmysqlsql
Ryann Galea
Ryann Galea

Reputation: 307

Query to return only records that are within 60 seconds of being posted

I have a query here that I use to find messages between users:

SELECT *
FROM msgs
WHERE
    (recipientid = '{$_SESSION['rider_id']}' AND authorid = '{$riderid}') 
    OR
    (authorid = '{$_SESSION['rider_id']}' AND recipientid = '{$riderid}')
ORDER BY datesent DESC

Now I have an AJAX call that will fire every 30 seconds once on the actual page to dynamically post new messages and I want it to grab any messages that have been posted that are within 30 seconds of being posted. And I cannot get it to work. I tried this:

SELECT *
FROM msgs
WHERE
     (recipientid = '{$_SESSION['rider_id']}' AND authorid = '{$authorrider['id']}')
     OR
     (authorid = '{$_SESSION['rider_id']}' AND recipientid = '{$authorrider['id']}')
     AND
     (datesent >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 SECOND))

But it still returns all the messages. Not ones just within the last 30 seconds.

I also need to set a status to 1 once it has been pulled, so if someone makes two replies within the 30 seconds it won't be pulled and .prepended () twice.

So once I can get the records I need, I will have to run a SET query to set the status. My MySQL isn't really up to polish to know how to accomplish this.

Another way I just thought of is setting status to 1 whenever it is retrieved. Then my AJAX query can just pull the records that status = 0.

Upvotes: 1

Views: 1129

Answers (4)

Paul Roub
Paul Roub

Reputation: 36438

You need to combine your ID criteria in parentheses. As it is, if your recipientid and authorid criteria match, anything after the OR (including the date) will never be checked.

So:

SELECT * FROM msgs WHERE 
 ((recipientid = '{$_SESSION['rider_id']}' AND authorid = '{$authorrider['id']}') OR
  (authorid = '{$_SESSION['rider_id']}' AND recipientid = '{$authorrider['id']}')) AND
 (datesent >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 SECOND))

Upvotes: 1

Dave Briand
Dave Briand

Reputation: 1734

I try to remove any thinking a db has to do and make the client do the work. For this, I would send a timestamp minus 30 seconds in the ajax query, instead of making the DB figure it out.

If this doesn't make sense I'd like to hear why.

Upvotes: 0

matiit
matiit

Reputation: 8017

SELECT * FROM msgs WHERE (recipientid = '{$_SESSION['rider_id']}' AND authorid = '{$authorrider['id']}') OR (authorid = '{$_SESSION['rider_id']}' AND recipientid = '{$authorrider['id']}') AND (DATE_DIFF(datesent,NOW()) > 30))

Try that

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I think your problem is the lack of parentheses on the where clause. Try this:

SELECT *
FROM msgs
WHERE ((recipientid = '{$_SESSION['rider_id']}' AND authorid = '{$authorrider['id']}') OR 
       (authorid = '{$_SESSION['rider_id']}' AND recipientid = '{$authorrider['id']}')
      ) AND 
      (datesent >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 SECOND));

Upvotes: 3

Related Questions