April 23rd, 2013, 07:15 AM
-
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 int) DAY
Any help will be greatly appreciated.
April 23rd, 2013, 12:33 PM
-
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
April 23rd, 2013, 02:22 PM
-
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!
April 23rd, 2013, 03:13 PM
-
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
April 23rd, 2013, 03:17 PM
-
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
April 23rd, 2013, 03:22 PM
-
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