0

I have a table that needs to be updated every 5 days by members. The event scheduler is to set any stale row to zero, if the row's last update is older than 5 days. My development environment is:

Windows 7, 32bit.
MySQL 5.5.39
PHP 5.4.45

My first query was: 

$query = 'CREATE EVENT event_name
ON SCHEDULE EVERY 5 HOUR 
ON COMPLETION PRESERVE ENABLE 
DO
UPDATE database_name.table_name SET table_column_name = 0 WHERE (CURRENT_TIMESTAMP - last_update_date) > 5 DAY'

I ran the script in PHP but the browser simply stopped. Displaying 'Server not found'.

Then I tested it in phpMyAdmin. This time I get:

#1064 - You have an error in your SQL syntax. Check the mysql manual that corresponds to your mysql server version for the right syntax to use near 'DAY'.

I checked MySQL Reference Manual (for version 5.6, though). My query seems correct, unless I'm missing something. So I changed the query to:

CREATE EVENT event_name
STARTS '2015-11-01 00:00:00'
ENDS '2030-11-01 00:00:00'
ON SCHEDULE EVERY 5 HOUR 
ON COMPLETION PRESERVE ENABLE 
DO
UPDATE database_name.table_name SET table_column_name = 0 WHERE (NOW() - last_update_date) > 5 DAY

Still got the same results. In reference to a related topic ((How to schedule a stored procedure in MySQL)), there's no event_scheduler in my php info.

Please help.

Community
  • 1
  • 1
Yemi A.
  • 49
  • 1
  • 4
  • One issue might be that CURRENT_TIMESTAMP returns only time values and it seems last_update_date is datetime,nevermind DAY is unnecessary.YOu might want `TIMESTAMPDIFF` function – Mihai Nov 03 '15 at 09:20
  • You're right, last_update_date is DATETIME. Thanks. I'll look up how to apply the function to my query. – Yemi A. Nov 03 '15 at 10:10

1 Answers1

1

There seems to be a problem with your the query you are trying to execute on the event. You could use DATE_SUB() assuming last_update_date is datetime.

UPDATE database_name.table_name SET table_column_name = 0 WHERE DATE_SUB(NOW(), INTERVAL 5 DAY) < last_update_date
Tristan
  • 3,301
  • 8
  • 22
  • 27
  • You're right to assume last_update_date is DATETIME. 'Cos it is. Thanks for an example on how to adapt the query to DATE_SUB(). Tested it and it worked. – Yemi A. Nov 03 '15 at 10:08