Schedule a MySQL Query - Cron?

Schedule a mysql query to run every hour - cron job?

[knippysing]

I have automation processes set up on a server to run certain database functions in access, convert the data and upload it to MySQL server off-site and this process runs every 2 hours. To format the date the way I need it, I have to run a query after each batch upload & things are better. I would like to try and automate this process as well and therefor a half hour update now takes about 5 minutes every 2 hours.

The query I'm trying to run is as follows:
ALTER TABLE `Dispatch`
CHANGE `Complete` `Complete` DATE DEFAULT NULL,
CHANGE `RecDate` `RecDate` DATE DEFAULT NULL
;
ALTER TABLE `Sales`
CHANGE `InvDate` `InvDate` DATE DEFAULT NULL,
CHANGE `EntDate` `EntDate` DATE DEFAULT NULL,
CHANGE `DueDate` `DueDate` DATE DEFAULT NULL
;
ALTER TABLE `SalesCrosstab` CHANGE `DueDate` `DueDate` DATE DEFAULT NULL
;
ALTER TABLE `Equip`
CHANGE `Install` `Install` DATE DEFAULT NULL,
CHANGE `Warranty` `Warranty` DATE DEFAULT NULL
;
ALTER TABLE `Equip_Temp` CHANGE `Install` `Install` DATE DEFAULT NULL

Does anyone know how to automate this? Does CRON have this ability? What all would I need to create the CRON job?

Thanks

[coopster]

Welcome to WebmasterWorld, knippysing.

It seems odd to me that you need to continually ALTER the table ...? Are you creating the table every time you run your process? Why not create the table once and script the date conversion?

Yes, you can also use cron to process your scripts at predetermined time intervals.

[Blackie]

You can use perl or php or any other scripting language to execute SQL commands.

Say you go for php, then you cron job can look like this: /path_to_php_compiler/php4 -w /path_to_script_to_run/script.php

[knippysing]

I a using Access2MySQL to upload new information from an access DB to the MySQL DB every hour. Therefor, it removes all data and then uploads new data, in turn, reformatting the date each time.

I think the cron is the best way to go. Thanks guys!

[Burner]

If you don't want to have the output from your php script being mailed to you every time the cron job is run you can use the -q flag on PHP.

php -q /path/to/script.php

Burner

[anshul]

Most control panel ( Linux ) I see fields:
E-mail to sent cron job acctivity: {email_address}
Minute, Hour, Day, Month, weekday
Command

Command is of format:
GET absolute_url_of_php_file > /dev/null

For per-hour execution:
I write *, */24, *, *, * respectively in Minute, Hour, Day, Month, weekday fields.