SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rows from the last 5 mins

    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
    Code:
    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. #2
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Solved
    UPDATE `texts` SET `messagesent` = 1 WHERE id = ".$row[0]." BETWEEN (NOW() - INTERVAL 5 MINUTE) AND NOW();")

  3. #3
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by txt3rob View Post
    what i would like to do is run the following query but also add from the last 5 mins
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 legend as always!

  6. #6
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i current used -
    Code:
    SELECT * FROM `texts` WHERE `content` IS NOT NULL AND `messagesent` = 0 LIMIT 5
    to pull in the sms to display.

    i though by doing
    Code:
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by txt3rob View Post
    ... it would then update any id with the timestamp of now and between 20 mins old
    not any id... just this one -- WHERE id = ".$row[0]."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    $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());
             
            
    }
         }



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •