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
Automate XML Insert into SQL
Moderator: General Moderators
Re: Automate XML Insert into SQL
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.
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.
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.
Re: Automate XML Insert into SQL
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
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
Cheers,
Steve