...

View Full Version : RSS Feed to Database?



greens85
06-24-2010, 11:56 AM
Hi all,

I assume this is in the correct forum, if not can an admin please move it...

I currently have a company who advertise a number of jobs on my site (non-profit) and to ease their load I am looking at having their jobs automatically uploaded from an RSS feed which they already have up and running...

What I need to do is take this feed and place the data into my database so it can be called back out with the rest of the jobs.

I don't have the faintest clue where to even begin, so any advice would be greatly appreciated.

Many thanks,

Greens85

mlseim
06-24-2010, 12:25 PM
We will need to see their RSS feed.
Give us the URL to that.

greens85
06-24-2010, 12:31 PM
We will need to see their RSS feed.
Give us the URL to that.

Hi mlseim,

Thanks for the quick response, here you go:

http://www.axcis.co.uk/rssuk.aspx

mlseim
06-24-2010, 08:48 PM
Here is an example of reading the RSS Feed (XML file),
and displaying the data. You can control how many
to view, and how much of it to view (number of characters).



<?php
// RSS Jobs Page -
$feed_url = "http://www.axcis.co.uk/rssuk.aspx";

# INITIATE CURL.
$curl = curl_init();

# CURL SETTINGS.
curl_setopt($curl, CURLOPT_URL,"$feed_url");
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_CONNECTTIMEOUT, 0);

# GRAB THE XML FILE.
$xmlFile = curl_exec($curl);

curl_close($curl);

# SET UP XML OBJECT.
# Use either one of these, depending on revision of PHP.
# Comment-out the line you are not using.
//$xml = new SimpleXMLElement($xmlFile);
$xml = simplexml_load_string($xmlFile);

// How many items to display
$count = 25;

// How many characters from each item
// 0 (zero) will show them all.
$char = 500;

foreach ($xml->channel->item as $item) {
if($char == 0){
$newstring = $item->description;
}
else{
$newstring = substr($item->description, 0, $char);
}
if($count > 0){
//in case they have non-closed italics or bold, etc ...
echo"</i></b></u></a>\n";
echo"
<div style='font-family:arial; font-size:.8em;'>
<b>{$item->title}</b><br />
$newstring ... <a href='{$item->guid}'>read more</a>
<br /><br />
</div>
";
}
$count--;
}
?>


This is what the output looks like:
http://www.catpin.com/jobs.php

Now, with some tweaking, you can determine what data to collect,
and how to display it. Then, when that's figured out, instead of
displaying it, we need to get it into your database ....
So we'll need information about your database.
database name
table name
column names
etc.




.

greens85
06-28-2010, 01:27 PM
Hi,

Apologies for the very delayed response...

The company have now actually given me a pure XML feed.

What impact does this have, I would imagine it makes things simpler?

The XML feed can be seen here:

http://axcis.klenka.com/rss.aspx

many thanks,

Greens85

mlseim
06-28-2010, 06:27 PM
It's the very same PHP script, with a few modifications:

Here is my example script:


<?php
// RSS Jobs Page -
$feed_url = "http://axcis.klenka.com/rss.aspx";

# INITIATE CURL.
$curl = curl_init();

# CURL SETTINGS.
curl_setopt($curl, CURLOPT_URL,"$feed_url");
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_CONNECTTIMEOUT, 0);

# GRAB THE XML FILE.
$xmlFile = curl_exec($curl);

curl_close($curl);

# SET UP XML OBJECT.
# Use either one of these, depending on revision of PHP.
# Comment-out the line you are not using.
//$xml = new SimpleXMLElement($xmlFile);
$xml = simplexml_load_string($xmlFile);

// How many items to display
$count = 25;

// How many characters from each item
// 0 (zero) will show them all.
$char = 1000;

foreach ($xml->job as $item) {
if($char == 0){
$newstring = $item->description;
}
else{
$newstring = substr($item->description, 0, $char);
}
if($count > 0){
//in case they have non-closed italics or bold, etc ...
echo"</i></b></u></a>\n";
echo"
<div style='font-family:arial; font-size:.8em;'>
Title: <b>{$item->title}</b><br />
Location: <b>{$item->location}</b><br />
Job Type: <b>{$item->Jobtype}</b><br />
Full/Part: <b>{$item->Full_Part}</b><br />
Salary: <b>{$item->Salary}</b><br />
$newstring ... <a href='{$item->url}'>read more</a>
<br /><br />
</div>
";
}
$count--;
}
?>



This is the working example of the script output ...
http://www.catpin.com/jobs.php

Now, you need to put that data into your database.
One thing to be aware of ... they cut-off their descriptions.
That means, you might not ever be able to show the complete
description for each job ... but a user can click on a link to
go directly to their site for the full description.



.

greens85
06-29-2010, 01:31 PM
Hi mlseim,

Many thanks for that, really really useful.

I have managed to get it into the database with the following code:


$title = $item->title;
$location = $item->location;
$jobtype = $item->Jobtype;
$hours = $item->Full_Part;
$salary = $item->Salary;

$qc = "SELECT jobid FROM jobs ORDER BY jobid DESC";
$rc = mysql_query($qc) or die (mysql_error());
$ac = mysql_fetch_array($rc);
$jobid = $ac[0] + 1;

$query = "INSERT INTO jobs (jobid, employerid, position, country, contract, hour, salary, description)
VALUES ('$jobid', '117', '$title', '$location', '$jobtype', '$hours', '$salary', '$newstring')";
$result = mysql_query($query) or die (mysql_error());

I have a slight problem in that some of their values don't match my options.. as an example:

Their value for a full time position is expressed as Full-Time whereas my is simply Full Time... which would normally be selected from a drop down.

There are a number of other aswell;

London - SW (theirs) :: London (mine)
Contract (theirs) :: Permanent or Temporary (mine)
Part-Time (theirs) :: Part Time (mine)

This obviously means that those particular jobs won't display under search results etc. Is this something I can do something about with the PHP? Or would they have to change the values they were sending?

They are also not providing the same amount of information that they do once you click though to the job, but I guess this is something that they have decided upon, and can't be fixed with PHP?

Many thanks,

Greens85

mlseim
06-29-2010, 01:50 PM
Do all the adjustments before you save it in your database ...

Sort of like this ....



$title = $item->title;

// convert Job Type text.
$jobtype="Permanent";
$xjobtype = $item->Jobtype;
if($xjobtype == "Contract"){
$jobtype="Temporary";
}

// convert Location text.
$xlocation = $item->location;
$location=$xlocation;
if(strpos($xlocation,"London")){
$location="London";
}
if(strpos($xlocation,"England")){
$location="England";
}

// convert Full/Part time text.
$xhours = $item->Full_Part;
$hours="Part Time";
if($xhours == "Full-Time"){
$hours="Full Time";
}

$salary = $item->Salary;

$qc = "SELECT jobid FROM jobs ORDER BY jobid DESC";
$rc = mysql_query($qc) or die (mysql_error());
$ac = mysql_fetch_array($rc);
$jobid = $ac[0] + 1;

$query = "INSERT INTO jobs (jobid, employerid, position, country, contract, hour, salary, description)
VALUES ('$jobid', '117', '$title', '$location', '$jobtype', '$hours', '$salary', '$newstring')";
$result = mysql_query($query) or die (mysql_error());



EDIT:
And yes, you can only deal with the information they are giving you.


and I made a couple of typos ... so the script above is fixed.




.

greens85
06-29-2010, 02:32 PM
Thanks for that...

Taking the location one as an example, how would I add many other locations to that:

Basically I need to convert all of the following to simply 'London';

London - Central
London - East
London - North
London - NW
London - SE
London - SW
London - West

Would it be something like so:


// convert Location text.
$xlocation = $item->location;
$location=$xlocation;
if(strpos($xlocation,"London")){
$location="London";
}

if(strpos($xlocation,"London - Central")) {
$location="London";
}

if(strpos($xlocation,"London - East")) {
$location="London";
}

etc...

Or have I completely misunderstood that!

mlseim
06-29-2010, 04:34 PM
The part below should work as-is.
What it does is simply look for the string "London" within the location name.
It doesn't care what else is in the string ... only cares about the word "London".
If it finds the string (which would make it true), it simply makes it "London".

Are you saying this isn't working?



// convert Location text.
$xlocation = $item->location;
$location=$xlocation;
if(strpos($xlocation,"London")){
$location="London";
}
// do the same thing with England ...
if(strpos($xlocation,"England")){
$location="England";
}

greens85
06-30-2010, 10:19 AM
The part below should work as-is.
What it does is simply look for the string "London" within the location name.
It doesn't care what else is in the string ... only cares about the word "London".
If it finds the string (which would make it true), it simply makes it "London".

Are you saying this isn't working?



// convert Location text.
$xlocation = $item->location;
$location=$xlocation;
if(strpos($xlocation,"London")){
$location="London";
}
// do the same thing with England ...
if(strpos($xlocation,"England")){
$location="England";
}


Ahhh, no I wasn't saying it wasn't working I was just wondering how I would add several conditions to it (if that makes sense)... I have however got it working this way, which may not be as good as your way.


// create a function to map fields
function do_replace($replace,$subject){
foreach($replace as $key=>$val){
$subject = str_replace($key,$val,$subject);
}

return $subject;
}

// create an array to convert values
$replace = array(
'Buckinghamshire' => 'South East',
'Essex' => 'Eastern',
'Hertfordshire' => 'South East',
'London - Central' => 'London',
'London - East' => 'London',
'London - North' => 'London',
'London - NW' => 'London',
'London - SE' => 'London',
'London - SW' => 'London',
'London - West' => 'London',
'North West England' => 'North West',
'Surrey' => 'South East',
'Part-Time' => 'Part Time',
'Full-Time' => 'Full Time',
'Contract' => 'Temporary',
'Permanent' => 'Permanent'
);

$location = do_replace($replace,$location);
$hours = do_replace($replace,$hours);
$jobtype = do_replace($replace,$jobtype);

As I say it works which will do for me, however I do appreciate it may not be the most efficient way of doing it.

mlseim
06-30-2010, 12:17 PM
Your way is good for you ... because you have specific, unique conversions
that don't follow general string matching. So keep what you have ... if it works,
then it's good to go.

Nicklen
07-12-2010, 06:35 AM
I'm having issues fetching details from an xml and inserting them to our database.file loaded in the website. i use this for tracking purposes



<?php

require_once('db.php');

$fileName = 'ems.xml';

if (file_exists($fileName)) {
$xml = simplexml_load_file($fileName);
} else {
exit('Failed to open ems.xml.');
}


$icounter = 0;
$ecounter = 0;
$icount = 0;
$ecount = 0;
$unloaded = array();
$ui = 0;

$fld1 = array();
$fld2 = array();


foreach($xml->Item as $ikey => $Item) {

$item = get_object_vars ($Item);

$Id = '';
$MailClass = '';
$Content = '';
$Weight = '';
$Currency = '';
$Value = '';
$OrigCountryCd = '';
$DestCountryCd = '';
$PostalStatusCd = '';


extract($item,EXTR_OVERWRITE);

$Id = $item['@attributes']['Identifier'];

$Currency = get_object_vars($Currency);
$Value = get_object_vars($Value);

$Currency = (count($Currency) == 1) ? $Currency[0] : '';
$Value = (count($Value) == 1) ? $Value[0] : '';


$sql = "INSERT INTO `item` (`Id`,`MailClass`,`Content`,`Weight`,`Currency`,`Value`,`OrigCountryCd`,`DestCountryCd`,`PostalStatu sCd`)
VALUES ('$Id','$MailClass','$Content','$Weight','$Currency','$Value','$OrigCountryCd','$DestCountryCd','$Po stalStatusCd')";

$result = mysql_query($sql);
$icount++;
$ItemId = $Id;

$afrows = mysql_affected_rows();

if ( $afrows > 0 ) {

$icounter += $afrows;

foreach ($Event as $ekey => $Evt) {

$Code = '';
$Date = '';
$OfficeCd = '';
$OperatorName = '';
$ConditionCd = '';
$RetentionReasonCd = '';
$DeliveryAgentCd = '';
$NonDeliveryReasonCd = '';
$NonDeliveryMeasureCd = '';
$SignatoryNm = '';
$DeliveryLocation = '';

$event = get_object_vars($Evt);
extract($event,EXTR_OVERWRITE);

$Date = date('YmdHis', strtotime($Date));

$sql = "INSERT INTO `event` (`Code`,`Date`,`OfficeCd`,`OperatorName`,`ConditionCd`,`ItemId`,
`RetentionReasonCd`,`DeliveryAgentCd`,`NonDeliveryReasonCd`,`NonDeliveryMeasureCd`,`SignatoryNm`,`De liveryLocation`)
VALUES ('$Code','$Date','$OfficeCd','$OperatorName','$ConditionCd','$ItemId',
'$RetentionReasonCd','$DeliveryAgentCd','$NonDeliveryReasonCd','$NonDeliveryMeasureCd','$SignatoryNm ','$DeliveryLocation')";

$result = mysql_query($sql);

$ecount++;
if ( mysql_affected_rows() > 0 ) {
$ecounter += mysql_affected_rows();
}

}

} else {

if (mysql_errno() != 1062)
$unloaded[$ui++] = $Id;

}

}


echo "XML File contains $icount items and $ecount events <br> Loaded $icounter items and $ecounter events... <BR><br><hr>";

$unloaded = array_unique($unloaded);

if (count($unloaded) > 0) {
echo 'Unloaded Items <br>';

foreach($unloaded as $u) {
echo "$u <br>";
}
}
?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum