468,470 Members | 1,488 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,470 developers. It's quick & easy.

How to automatically update a MySQL database every day

uranuskid
Hey folks,

I'm curious about how to perform following task:

I need to update a MySQL database every day in terms of deleting expired entries.Therefore, I have to fields in the db named 'date' and 'expired' (which is the actual date ... How do I get that again?).

So, I want to run a script that compares both fields and deletes respective entries where both fields match.

I want to do this at the end of the day and the auto_increment field should be updated for the whole db.

If anyone could drop me some hints would be appreciated.

Thanks
uranuskid
Mar 11 '07 #1
8 29438
michaelb
534 Expert 512MB
I think the SQL to update your table may look like this:
Expand|Select|Wrap|Line Numbers
  1. delete from my_table where current_date >= expiration_date
  2.  
I am not sure about this:
>> the auto_increment field should be updated for the whole db.
You did not say anything about this field, and auto_increment is something that typically managed by the database (a new record ID, for example)

You can execute your script using the MySQL Event Scheduler.
It allows you create a new event, set the schedule, and provide the SQL for this event.

You may find these links helpful:
http://dev.mysql.com/tech-resources/...t-feature.html

http://dev.mysql.com/doc/refman/5.1/en/events.html
Mar 11 '07 #2
ronverdonk
4,258 Expert 4TB
I want to do this at the end of the day and the auto_increment field should be updated for the whole db.
When you want to reset your auto_increment field, you'll have to create a new table (like the one you have now) and insert your current table in that one.
That way the auto_increment field is re-started from the beginning.
After that delete the old table one and rename the new one.

Ronald :cool:
Mar 12 '07 #3
I think the SQL to update your table may look like this:
Expand|Select|Wrap|Line Numbers
  1. delete from my_table where current_date >= expiration_date
  2.  
You may find these links helpful:
http://dev.mysql.com/tech-resources/...t-feature.html

http://dev.mysql.com/doc/refman/5.1/en/events.html
Thanks for the help.

Just to get it right, what I need to do is create a recurrent even scheduler that updates my current date and then compares it with expiring date and then deletes all entries that are expired. Do you reckon I'll get problems doing this with MyPHPadmin on the webserver?

Cheers,
uranuskid
Mar 12 '07 #4
michaelb
534 Expert 512MB
If you're asking whether you can use MyPHPadmin to create an event I don't see why not.
Provided that you have required privileges/permissions to the database, you should be able to run the "CREATE EVENT ..." sql just like you run "CREATE TABLE ..." or any other DML, using MyPHPadmin or any other interface you may have.

I don't quite understand why you need the current_date column.
Unless there's something you did not tell us, I would drop this nuisance and compare column expiration_date with result of CURRENT_TIMESTAMP() or CURRENT_DATE(), or whichever date/time function is appropriate for your situation.
In this case your SQL may look like this:

Expand|Select|Wrap|Line Numbers
  1. delete from my_table where expiration_date >= CURRENT_DATE()
  2.  
Mar 12 '07 #5
Also ich hab das jetzt alles mal ausprobiert und das entsprechende im Manula gelesen. Kling alles sehr logisch. Nur leider geht es bei mir nicht. Liegt das an meiner MYSQL version (4.1.21) oder an meinem code:
Expand|Select|Wrap|Line Numbers
  1. CREATE event delete 
  2. on schedule every 1 day starts at timestamp '2007-03-25 23:59:00' 
  3. do delete from rides where current_date()v>=v RideDate();
Mar 25 '07 #6
Also ich hab das jetzt alles mal ausprobiert und das entsprechende im Manula gelesen. Kling alles sehr logisch. Nur leider geht es bei mir nicht. Liegt das an meiner MYSQL version (4.1.21) oder an meinem code:
Expand|Select|Wrap|Line Numbers
  1. CREATE event delete 
  2. on schedule every 1 day starts at timestamp '2007-03-25 23:59:00' 
  3. do delete from rides where current_date()v>=v RideDate();
Sorry, that was German. Forgott that I'm on an English forum. Well, I tried all the advices and they seem logic, also I read the respective Manual pages. However, it won't work. I wonder is it because of my version (4.1.21) or because of the wrong code (see above)

Cheers,
Frank
Mar 25 '07 #7
michaelb
534 Expert 512MB
I think you are correct with your first assumption; the Event Scheduler Syntax manual says this:
MySQL 5.1.6 and later provides several SQL statements for working with scheduled events:

Does upgrading to v. 5.1 look like a feasible option for you?
Mar 26 '07 #8
Ahhu
1
There is a problem in the syntax you used:
Expand|Select|Wrap|Line Numbers
  1. on schedule every 1 day starts at timestamp '2007-03-25 23:59:00'
Should be only:
Expand|Select|Wrap|Line Numbers
  1. on schedule every 1 day starts '2007-03-25 23:59:00'
If you're using phpMyAdmin, just go to:
http://YOURDOMAIN/phpmyadmin/db_events.php?db=YOUR_DB
And you can quickly create as many events as you want without ever knowing the proper syntax for events.
But if you insist in DIY, double check your syntax with:
http://dev.mysql.com/doc/refman/5.1/...ate-event.html

Btw, thanks for your code, it was the first time I saw SQL events like this and I used your code for a similar purpose. It worked after I removed that "at timestamp".
Mar 4 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Carlos Eduardo Peralta | last post: by
7 posts views Thread by sime | last post: by
2 posts views Thread by Ville Mattila | last post: by
reply views Thread by Hans | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.