Automate XML Insert into SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stevespi
Forum Newbie
Posts: 6
Joined: Fri May 01, 2009 5:34 am

Automate XML Insert into SQL

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Automate XML Insert into SQL

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
stevespi
Forum Newbie
Posts: 6
Joined: Fri May 01, 2009 5:34 am

Re: Automate XML Insert into SQL

Post 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>";
 
}
 
 
}
?>
 
stevespi
Forum Newbie
Posts: 6
Joined: Fri May 01, 2009 5:34 am

Re: Automate XML Insert into SQL

Post 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
Post Reply