[SlimShade]

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

INSERT INTO `chatconfig` (`id`, `vckey`, `vcvalue`) VALUES ('63', 'archive_date', '30'); 

Here's the original code that's working OK

// 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

UPDATE chatmessage c cross join
     SET c.archive = '1' 
     WHERE chatconfig.vckey = 'archive_date' and
           c.dtmcreated < '$date' - INTERVAL cast(chatconfig.vcvalue as int) DAY 

Any help will be greatly appreciated.

[r937]

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

[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!

[Frank Grimes]

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.

[r937]

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

[Frank Grimes]

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.