Rows from the last 5 mins

Databases
1

i have a table where a row has current_timestamp as automatic

what i would like to do is run the following query but also add from the last 5 mins

SELECT * FROM `texts` WHERE `content` IS NOT NULL AND `messagesent` = 0

the col with the date is called datereceived and date format is 2013-08-31 11:54:19

I am awful at time query’s can any one assist?

2

Solved

UPDATE texts SET messagesent = 1 WHERE id = “.$row[0].” BETWEEN (NOW() - INTERVAL 5 MINUTE) AND NOW();")

3

nope thought i had fixed it and i had not. as i’ve not told it which col to check for time!
Dozy me!

4 
SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star 
  FROM texts 
 WHERE content IS NOT NULL 
   AND messagesent = 0
   AND datereceived BETWEEN CURRENT_TIMESTAMP - INTERVAL 5 MINUTE
                        AND CURRENT_TIMESTAMP
5

r937 legend as always!

6

i current used -

SELECT * FROM `texts` WHERE `content` IS NOT NULL AND `messagesent` = 0 LIMIT 5

to pull in the sms to display.

i though by doing

UPDATE `texts` SET `messagesent` = 1 WHERE id = ".$row[0]." AND datereceived BETWEEN CURRENT_TIMESTAMP - INTERVAL 20 MINUTE AND CURRENT_TIMESTAMP

after the contents had been displayed it would then update any id with the timestamp of now and between 20 mins old and it appears to just update them after around 5 and i have checked the code to confirm it’s set to 20.

have i done something wrong?

7

not any id… just this one – WHERE id = “.$row[0].”

8

it appears to of done it all weirdly as i texted the number about 1 min ago and checked the database and it’s showing already as sent.


$result = mysql_query('SELECT * FROM `texts` WHERE `messagesent` = 0');  // Is there any texts to display? if not adverts to show.
if ($result) {
     if (mysql_num_rows($result) == 0) {
        include ('ads.php');
     }
     else {
        $sms = mysql_query("SELECT * FROM `texts` WHERE `content` IS NOT NULL AND `messagesent` = 0 LIMIT 5");  //Grab 5 messages to display
		while ($row = mysql_fetch_array($sms)) { 
		echo (' '.$row['content'].' ');
		mysql_query("UPDATE `texts` SET `messagesent` = 1 WHERE id = ".$row[0]." AND datereceived BETWEEN CURRENT_TIMESTAMP - INTERVAL 20 MINUTE AND CURRENT_TIMESTAMP") //updates 'messagesent' to show it has been sent out.
or die(mysql_error());
		 
		
}
     }
}