0

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.

1

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

  • That my friend is a little bit to much for me I guess. I'll take a look at it but I'm not really in to Windows based systems. However code is code so I'll see what I can make of it. ThnQ – Interactive Jan 16 '15 at 8:26
1

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( 'xxx@xxx.nl', '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.

0

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:

  1. Does not rely or require anything from server configuration
  2. Is triggered by visits to the site, so might not fire reliably on time
  • I'm impressed that WordPress has this. I'm working with WP for 4 years now and because I didn't ever needed this I never knew it has this. WP keeps surprising me. However I'll take a look and see what happens :-) – Interactive Jan 16 '15 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.