Execute a function every hour in the background

Question

Execute a function every hour in the background

Interactive

2015年1月26日 12:38

I'm in kind of a weird situation (for me)
My client has a website that runs on a custom CMS
He wants an online site that loads some data from the existing database.
This is an external MSSQL database and for security reasons a serialized table is created. So there is no way for me to "read" from the database.

I contacted the company that hosts and maintains the database and they said that they could automatically create an .csv export for me every hour and save it at a location on the server.

So I was very happy with this because this is a comma seperated .csv file.

I learned that I could import this to my WP MySQL database with the following:

LOAD DATA INFILE '../tmp/file.csv'
INTO TABLE $table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

So far so good. But now my headache starts. I want to do this every hour. So that if their database is altered mine get's updated. The file may overwrite every table/field in the database. (I only use it for reading)

I know I can create a cronjob to do this. I simply don't know where to do this. This task must run every hour. So this does not go in a function.php file or even a template.php file.

Come to think about it, maybe it shouldn't even be done in WordPress core/custom files.

However. Because I don't know where to initiate the function I can not update my database.

Hope somebody has a (alternative) solution.

M.

Topic mssql php database Wordpress

Category Web

Interactive · Accepted Answer · 2015年1月26日 12:38

So I made it happen :-)

There is one thing that I didn't really like to do but for the time being there is no alternative: The CronJob from the server

This PHP script runs in a single (no template) file:

$xmlData = file_get_contents(get_bloginfo('template_directory').'/import/external_db.xml');         
// read XML data string
$xml = simplexml_load_string($xmlData) or die("ERROR: Cannot create SimpleXML object");

 foreach( $xml as $user )
    {       
        $name = $user->firstname;
        $code = $user->code;
        $email = $user->email;
        $jobdescription = $user->jobdescription;
        $salary = $user->salary;        
        $sql = "INSERT INTO xxx_employees (name, code, email, jobdescription, salary) VALUES ('$name', '$code', '$email', '$jobdescription', '$salary')";
        mysql_query($sql) or die (mysql_error());
        echo 'Success';
    }

The only thing this script does is get the XML file from a certain place on a server and dumps it in a table I allready created in the database.

This needs to run every hour. So in my functions.php:

add_action( 'wp', 'prefix_setup_schedule' );
/**
 * On an early action hook, check if the hook is scheduled - if not, schedule it.
 */
function prefix_setup_schedule() {
if ( ! wp_next_scheduled( 'prefix_hourly_event' ) ) {
    wp_schedule_event( time(), 'hourly', 'prefix_hourly_event');
}
}


add_action( 'prefix_hourly_event', 'prefix_do_this_hourly' );
/**
 * On the scheduled action hook, run a function.
 */
function prefix_do_this_hourly() {
//wp_mail( '[email protected]', 'Auto mail every hour', 'Automatic scheduled email from WordPress.');
}

This so called "CronJob" does a dump in the database every hour (I need to add a function that clears the database before inputting new data) The only problem that I had and still have with this is that WordPress doesn't do this every hour unless there is a request to load the page. If there are no visitors there will be no update.

To fix this problem I created a CronJob on my server to execute a request every hour so that the database is updated.

I hope I will find a solution to replace the CronJob from my server.

But thats it. Now I need to execute the XML upload function to my functions.php and make sure the database is flushed before filling it with new data.

@Rarst: Thanks for the WP-Cron heads up. Very usefull.

Rarst · Accepted Answer · 2015年1月15日 17:41

WordPress has a WP-Cron mechanism for running scheduled tasks. It is used internally for update checks and other tasks, as well as extensively by plugins.

Pro/con break down is pretty much:

Does not rely or require anything from server configuration
Is triggered by visits to the site, so might not fire reliably on time

Brandt Solovij · Accepted Answer · 2015年1月15日 16:58

I setup something similar. Using IIS - look into writing a .bat file which fires off a php shell script to do whatever it is you need to accomplish.

once you have that bat file working, setup a "scheduled task" in IIS http://technet.microsoft.com/en-us/library/cc721871.aspx

if you are on a linux system, then use crontab https://www.freebsd.org/doc/handbook/configtuning-cron.html

If you on a hosted, non-vps environment, then you will need to sort out how your site ISP handles this sort of task - each will be different and dependent on contracts / setup

Execute a function every hour in the background

About