22Ryann 22Ryann - 25 days ago 4
PHP Question

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.

Answer

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));
Comments