PHP MySQL – Once a Week Operations

I've got a database that processes an order - the Order is inserted at the beginning of the process but if the process is not complete (they left the page etc) the "confirmed" field is not filled in, this allows the admin to know which orders were complete or not.

At the moment I don't have the luxury of adjusting the way the website manages the data, I can however add some sort of weekly cleanout of these database fields where "confirmed" = 0.

How would I go about doing this automatically? Is there a way to run a query like this on a weekly, monthly, yearly basis?

Edit:

What if I added that the website is stor开发者_StackOverflow社区ed on a restricted, shared server where I have no access to the box itself?

You can use CRON to perform this job. But if you need to clean your database from incompleted orders only to show completed ones for humans and you don't export them to other places (e.g. manager visits backend once per day and looks for completed orders manually), i suggest you not to hire CRON for this. You can add few lines of code which will remove old entries from database each time backend was accessed.

DELETE queries are executed really fast so there will be no problems with perfomance. Real data deletion will be performed only by request. Also you don't need to have CRON (some cheap hosting providers do not give access to this service).

I usually create a PHP page that does the action and then schedule that to run using cURL and a CRON job.

If it's a Unix-like box, I'd put the cleanup in a PHP CLI script (that is, a PHP script that runs from the command line rather than in a web server module) and invoke that from a cronjob. Cron is a daemon for this exact purpose: Run things at predefined intervals. Cronjobs are defined in a crontab file, usually in /etc/crontab (for root) and ~/.crontab (for regular users); many distributions offer tools to safely edit this file.

Try man cron and man crontab for details. Also: http://en.wikipedia.org/wiki/Cron

setup a cron job and do a wget to run the script you want to automate. look at the link for more info http://www.phpmyvisites.us/faq/crontab-launch-automatically-statistics-archiving-process-every-62.html.

Write a short PHP script that updates/deletes the mysql records,

Then, if you have access to the linux/unix box, type crontab -e, you will then be prompted with the option to choose which editor you would like to view the current cronjobs, select the default and add this line:

0 0 * * 0 /usr/bin/php /path/to/script.php

Please check if /usr/bin/php exists before you set this up (you will need to install php-cli incase it does not exist, or the file might be also at /usr/sbin/php).

This cronjob will run once a week, if you want to change the frequency, use the manpages to learn how.

Also, are you sure you would like to run this script automatically? from what I understand you will need someone to first see the incomplete orders and only then to clean them up. if you run this cronjob you might be in a situation where someone did not complete his order but any evidence to that was deleted.

Hope this helps,