parse an rss feed and update/insert/delete rows

Question

Hi all

I'm trying to parse multiple RSS feeds and If they change, thus update my records in my MySQL table.

Currently, I have a script that inserts items of RSS Feeds (just post in the url in a form and submit). This inserts the following into my table: title, rss_url, description, price, discount, total

This all works perfectly well.

The next part is a script that updates the rows if they change in the RSS, but the only changes are if the price or discount update. This also works great

What I'm also looking to do is: If an item in the RSS feed is removed, then my script needs to detect this and delete the row or insert a flag into my table to say its been deleted...

My code is quite long winded:

$result = mysql_query("SELECT * from easy_contents");
while($row = mysql_fetch_array($result))
{

$articles = array();
$easy_url = $row['rss_url'];

$rawFeed = file_get_contents($easy_url);
$xml = new SimpleXmlElement($rawFeed);


$channel = array();
$channel['title']       = $xml->channel->title;
$channel['link']        = $xml->channel->link;
$channel['description'] = $xml->channel->description;


foreach ($xml->channel->item as $item)
{
$article = array();
$article['title'] = $item->title;
$article['link'] = $item->link;
$article['description'] = (string) trim($item->description);

//strip out all the HTML tags
$item->description = str_replace('<table><tr><td width="110">','', $item->description);
$item->description = str_replace('</table>','', $item->description);
$item->description = str_replace('</td>','', $item->description);
$item->description = str_replace('<td>','', $item->description);
$item->description = str_replace('<br />','', $item->description);
$item->description = str_replace('<b>','', $item->description);
$item->description = str_replace('</b>','', $item->description);
$item->description = str_replace('</tr>','', $item->description);

//find all url encoded £ signs and find the string after
//string will be a price
preg_match_all('/&#xA3;([0-9.]+)/', $item->description, $results);
foreach ($results as $k => $v) {
}

//find the url encoded £ sign and append the price
$all = '&#xA3;'.$v[0];
$price_stripped = str_replace($all, '', $item->description);
$desc = preg_match('/&#xA3;([0-9.]+)/', $item->description);

//find the discount deleviry cost from the rss using the ~#&pound;NUMBER
//this is the discount
preg_match_all('/~#&pound;([0-9.]+)/', $item->description, $discount);
foreach ($discount as $d => $disc) {
str_replace("~#&pound;","", $disc[0]);
}

//find the remaining £PRICE and this is the delivery cost
//this is the delivery_cost
preg_match_all('/&pound;([0-9.]+)/', $item->description, $delivery_cost);
foreach ($delivery_cost as $del => $deliv) { 
}

 //find the | char and find the string after it
//this is the retailer_message
preg_match_all('/\|(.*?)\./',$item->description,$match);           
foreach ($match as $rel => $retail) { 
$retail[0] = str_replace("| ","", $retail[0]);
$retail_mess = str_replace(" On","On", $retail[0]);

 }   

 $total = $v[0] + $deliv[0] - $disc[0];

 $sql = "UPDATE easy_contents SET delivery_cost = '$deliv[0]', price = '$v[0]', total = '$total' WHERE rss_url = '$row[rss_url]' AND title = '$item->title' AND description = '$price_stripped' ";
 if(!$query = mysql_query($sql)) {
     echo "Error on line ".__LINE__.". ".mysql_error().".<br />\nQuery: ";
     exit;
 }
 echo "Query OK. <br />\nUpdated rows: ".mysql_affected_rows().".<br />\nQuery: ";
   }   
  }

This updates the row in the database depending if the rss item changes.

Can anyone provide a snippet of how I'd detect if an item in the rss is deleted and also the php/mysql to then delete such row from my table?

Thank you

Answer 1

If simply replacing your data with new data from the RSS feed won't work for you, you could run through a few steps:

1. Query all from DB. Parse into array with ID
2. Parse RSS into Array with ID.
3. Compare arrays. The difference will be the ID's to delete from your DB.
4. Loop through difference array and delete.

I do something similar on an app I wrote. It's a long solution, but once you get the bugs out it works really well.