[RESOLVED] Read RSS Feed and Store in Database?

[djalecc]

Hi

I'm trying to find out how easy/hard it would be to read a RSS feed and then place the data in MySQL

the feed is here... http://www.highways.gov.uk/rssfeed/rss.xml

The terms state that if I want to use this feed then I need to cache the data every 5 minutes to my server, where I can then output it as often as I like [ to save their server ]

So, I was thinking if I made a table for all the values, and wrote a script that could read the RSS feed every 5 minutes via a CRON JOB I could Empty the current data in the table, read the feed then add the new data.

BUT ...

I also want to extract the LAT & LNG held in the <link>URL</link> tabs

<link>http://www.trafficengland.co.uk/map/browse.cgi?client=tcc&lat=52.512376&lon=-1.726174&scale=200000&icon=x&currentChk=checked</link>

RSS isn't something I've even got in to, so I'm scratching my head where to start here...

Any help on the reading of the feed and/or breaking it up in to its values [ITEMS], and also just the LAT & LNG in the LINK would be most welcome.

Example :

- <item>
  <title>M42 Warwickshire | Northbound | Vehicle fire, 2 lanes closed</title> 
  <description>On the M42 northbound entry slip at junction J8 , there are currently delays of 20 mins due to a vehicle on fire closing two lanes. Normal traffic conditions expected from 7:45 pm.</description> 
  <author>Highways Agency (NTCC)</author> 
  <pubDate>Wed, 06 Jun 2007 17:41:45 GMT</pubDate> 
  <link>http://www.trafficengland.co.uk/map/browse.cgi?client=tcc&lat=52.512376&lon=-1.726174&scale=200000&icon=x&currentChk=checked</link> 
  <guid>U-07-0606-0162</guid> 
  </item>

Would generate...

$title="M42 Warwickshire | Northbound | Vehicle fire, 2 lanes closed";
$description="On the M42 northbound entry slip at junction J8 , there are currently delays of 20 mins due to a vehicle on fire closing two lanes. Normal traffic conditions expected from 7:45 pm";
$author="Highways Agency (NTCC)";
$pubDate="Wed, 06 Jun 2007 17:41:45 GMT";
$link="http://www.trafficengland.co.uk/map/browse.cgi?client=tcc&lat=52.512376&lon=-1.726174&scale=200000&icon=x&currentChk=checked";
$guid="U-07-0606-0162";
$LAT="{the lat in the $link URL}";
$LNG="{the lng in the $link URL}";

Any help on this would be welcome, there may be something already out there that does such a job, so I'm just aksing, if there is not, then maybe someone can point me to a good starting point where I can then write it myself.

🆒

[NogDog]

As far as the caching thing, what I might do is just have one script. When a user accesses it, I'd check for the existence and [man]filemtime/man of the cache file. If it's there and is <= 5 minutes old, I would just display its data. If it does not exist or is older than 5 minutes, then send a request to the RSS server for the latest data, parse it, display it, and save it as the cache file.

As far as parsing the data, you might want to look at the PEAR XML_RSS package. If you prefer to "roll your own" and are running PHP 5, take a look at the [man]SimpleXML[/man] functions.

[ad_aus]

An alternative approach would be to parse xml in javascript... since xml is very small. Browsers automatically cache retrieved file so by adding a "time parameter" as extension to the php file you could controll how offten any particular user "downloads" rss.

Of course this is client side solution and number of downloads referenced from your site would be dependant on how many concurrent users you have....

[djalecc]

Hi

I was thinking more of writing a script that is run by a cron job every 5 minutes.

When the script runs, it empties the current data from the database with the truncate command, then reads the RSS feed from the site & re-adds whatever there is in the feed at that time to the database again.

Thus the database would always be changing, but when people access or request the data it only comes from my site, thus I don't use any more of their bandwidth if say I had 100 users accessing the information at the same time.

Does this make sense?

[ad_aus]

It makes perfect sense, I'm after something very similar!

I'm not clear if you have a code to extract data into variables but to start, just one idea on "reading" lat/lng from the url: if you try "split" with = and & as delimiters it should divide your url into:

http://www.trafficengland.co.uk/map/browse.cgi?client   tcc   lat  52.512376  lon  -1.726174  scale  200000  icon  x  currentChk  checked

Then write [3] and [6] into a database... how? No idea! I'm on a similar "quest for knowledge"...

[djalecc]

OK, I've been working on this, and this is what I have done so far...

<?php

//include 'connect.php';// not saving in database currently... just outputing to screen

$URL="http://www.highways.gov.uk/rssfeed/rss.xml";

$f = fopen($URL, "r");
if($f){

$pre = "";
while(!feof($f))
{

$pre= fread($f, 1000);
$source = $source.$pre;
}
}
else
{
echo 'Unable to open '.$URL.'.';
die;
}

//extract the date into database
$datetime = date("Y-n-j");

$total= substr_count($source, "<item>");

//extract necessary information into database
$pos=0;
for($loop=0;$loop<$total;$loop++)
{
$line1 = strpos($source, "<title>", $pos);
$end1 = strpos($source, "</title>", $line1);
$line1 = $line1 + 7;
$end1 = $end1 - $line1;
$title = substr($source, $line1, $end1);
$title = convert($title);

$line2 = $line1 + $end1 + 1;
$line2 = strpos($source, "<description>", $line2);
$end2 = strpos($source , "</description>" , $line2);
$line2 = $line2 + 10;
$end2 = $end2 - $line2;
$category = substr($source , $line2, $end2);
$category = convert($category);

$line3 = $line2 + $end2 + 1;
$line3 = strpos($source , "<link>" , $line3);
$end3 = strpos($source , "</link>" , $line3);
$line3 = $line3 + 6;
$end3 = $end3 - $line3;
$link = substr($source , $line3 , $end3);
$link = convert($link);

$pos = $line3 + $end3 + 1;


echo"<br><br>$datetime<br>$title<br>$category<br>$link<hr>";

}

function convert($string)
{

$string = htmlspecialchars($string,ENT_QUOTES);
return $string;
}

?>

So this is grabbing the RSS feed, and outputs to the screen $title, $category and $link

All I need to do now is also read $link, and split it up so it also gives me

$LAT
$LON

from the link...

If anyone knows a quick easy way to do this, please let me know, my php is lame!

🙂

[djalecc]

Looks like I did it...

$pieces = explode("&", $link);
echo "<br>1 $pieces[0]<br>"; // piece1
echo "<br>2 $pieces[1]"; // piece2
echo "<br>3 $pieces[2]"; // piece3

$LAT=$pieces[1];
$LON=$pieces[2];

$LAT1=explode("amp;", $LAT);
$LAT2=$LAT1[1];

$LON1=explode("amp;", $LON);
$LON2=$LON1[1];

echo"<br><br>$datetime<br>$title<br>$category<br>$link<br><br><br>$LAT2<br>$LON2<hr>";

I've now got an output like so...

$datetime
$title
$category
$link
$LAT2
$LON2

All ready to input to database!

😃

[JMD]

what does your finished code look like? I am looking to do the same thing.