Page 1 of 1

Automate XML Insert into SQL

Posted: Mon Jun 01, 2009 3:13 am
by stevespi
Hello All,

Im struggling with were to start with a problem Im having...

Iv written a Job Board and I have a company that wants to publish lots of Jobs onto my board. Which is great... They have setup an XML feed, that I can pick up daily and then I simply convert into a CSV file using MS Access and upload into my database. Then I have some other SQL code that updates the correct Job Details so It becomes live on my board... This all works great, but I really need to automate the process. So it can pick up the xml file from a specific URL. Then to the upload into the SQL database and pass the data into the correct table...

Is there an easy way to do this? Im assuming i would need to run a Cron Job (but i have never actually used this... In pretty new to PHP).. I have googled lots of things, but I cannot find what im looking for...

Any help would be greatly appreciated or any guidance would be excellent.

Also If I can just simply not work it out, is there a company or individual I could pay to write the script for me. I know this sounds lazy, but Id rather get it 100% correct. As its information people are paying me to put on my site and id like to get it spot on.

Many thanks,

Steve

Re: Automate XML Insert into SQL

Posted: Mon Jun 01, 2009 3:38 am
by s.dot
This would be easy to do.

Use file_get_contents() on the URL to grab the XML string. Use SimpleXML() to grab the info you want. Update (query) your database with the info. :) Put it all on a cron job and you're good to go. crontab isn't controlled by php (usually), so you will need to access your control panel (if you're using one) to set a cron job to run, or go in manually and access the crontab program on the command line.

You can google how to set up a cron job if you need help with that part.

Re: Automate XML Insert into SQL

Posted: Mon Jun 01, 2009 9:49 am
by stevespi
Does the below look correct? I have been trying to work it out, I have not yet tested it. As I do not have access to my sever until tonight. If any of it looks wrong, can you help me at all.

Code: Select all

 
 
<?php
$jobcv = file_get_contents('http://www.examplerssfeed.com/rss.xml');
?>
 
<?
 
$xml = simplexml_load_file($jobcv);
 
foreach($xml->children() as $child)
{ 
$allData[] = $child;
}
 
 
foreach($allData as $key=>$value)
{
$name = $allData[$key]->name;
$age = $allData[$key]->age;
 
$title = explode(",",$allData[$key]->title);
$JobTitle = $title[0];
$Joblocation = $title[1];
 
//print $name."<BR>".$age."<BR>".$JobTitle."<BR>".$Joblocation."<BR><BR>";
$sql = "INSERT INTO tbl_test (name,age,jobtitle,joblocation) VALUES('".$name."','".$age."','".$JobTitle."','".$Joblocation."')";
 
print $sql."<br><br><br>";
if(mysql_query($sql))
{
// print "Data Inserted Successfully";
 
}
else
{
// print "Data could not be Inserted Successfully<br><br>";
 
}
 
 
}
?>
 

Re: Automate XML Insert into SQL

Posted: Mon Jun 01, 2009 3:17 pm
by stevespi
ok i have tested the script and with a little bit of manipulation. It eventually works. Well Kind of.... The problem im having, is the php fails if in the string of the xml it contains an "&" is there a way to stop it from failing when it contains the "&".

Cheers,

Steve