#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    3
    Rep Power
    0

    How to change - INTERVAL 7 DAY? with an int


    Hello All,

    I have this sql which I have always had to change the interval value by file edit & would like to get the 30 from the database archive_date vcvalue.

    I have added an entry to the chatconfig table called archive_day
    PHP Code:
    INSERT INTO `chatconfig` (`id`, `vckey`, `vcvalue`) VALUES ('63''archive_date''30'); 
    Here's the original code that's working OK
    PHP Code:
    // This function will archive ALL messages greater than 30 days
    function archive()
    {
        
    // date function minus ? days tells us what we need to select and move it over
        
    $date date('Y-m-d');
        
    $link connect();        
        
         
    $sqlresult mysql_query("UPDATE chatmessage SET archive = '1' WHERE dtmcreated < '$date' - INTERVAL 20 DAY",$link) or die(' Query failed: 
        '
    .mysql_error().": ".$query);*/
        
        
    // This adds all information from chatmessage to archivechatmessage
        
    $sqlArchive mysql_query("INSERT INTO archivechatmessage SELECT * FROM chatmessage WHERE archive = '1'",$link) or die(' Query failed: '.mysql_error().": ".$query);
        
        
    // This deletes from chatmessage
        
    $sqlDelete mysql_query("DELETE FROM chatmessage WHERE archive = '1'",$link) or die(' Query failed: '.mysql_error().": ".$query);

    // end of function    

    I tried this but it produced an error

    PHP Code:
    UPDATE chatmessage c cross join
         SET c
    .archive '1' 
         
    WHERE chatconfig.vckey 'archive_date' and
               
    c.dtmcreated '$date' INTERVAL cast(chatconfig.vcvalue as intDAY 
    Any help will be greatly appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Code:
    UPDATE chatmessage 
    CROSS
      JOIN chatconfig
       SET chatmessage.archive = '1'  
     WHERE chatconfig.vckey = 'archive_date'  
       AND chatmessage.dtmcreated < 
           CURRENT_DATE - INTERVAL CAST(chatconfig.vcvalue AS UNSIGNED) DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    3
    Rep Power
    0
    Hi r937,

    You wouldn't believe how grateful I am for this working reply, I have waisted hours trying to sort it out.

    But a question 4u ?
    You replaced '$date' with CURRENT_DATE, what's the difference as $date = date('Y-m-d'); can now be removed, why would it of been done that way in the first place if CURRENT_TIME could of been used.

    Thank You!
  6. #4
  7. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    97
    Originally Posted by SlimShade
    Hi r937,

    You wouldn't believe how grateful I am for this working reply, I have waisted hours trying to sort it out.

    But a question 4u ?
    You replaced '$date' with CURRENT_DATE, what's the difference as $date = date('Y-m-d'); can now be removed, why would it of been done that way in the first place if CURRENT_TIME could of been used.

    Thank You!
    Why would you create a new variable and populate it with the current date if there is a constant available to do the same thing? Saves overhead; increases performance.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by Frank Grimes
    Saves overhead; increases performance.
    true, not by a whole lot, but still...

    also, it's cleaner

    plus, there's no need to go back from the sql to the php code to double-check that the variable was set correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    97
    Originally Posted by r937
    true, not by a whole lot, but still...

    also, it's cleaner

    plus, there's no need to go back from the sql to the php code to double-check that the variable was set correctly
    Excellent points as well.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo