Parsing RSS feeds and deleting/inserting records
Posted: Fri Oct 08, 2010 10:36 am
Hi all
I have a slight problem which I am struggling to come up with an idea/code for.
I am currently parsing RSS feeds and extracting out certain parts and then inserting these into a mysql database, which works well.
The problem comes when particular items are either added or removed from the RSS feed.
I need to do a script that, will detect such changes and either insert or delete from the database.
My MySQL tables is as follows:
What my script needs to do though is 2 parts, for example, is:
Deleting a row
If the row of id 17, is removed from the RSS feed, it needs deleting from the table. The only problem is though, the RSS feed doesn't have an id, so I am currently searching for the string 'On sale at XYZ' to do all of my work. So what the script needs to do is I guess loop through the rss_url of http://www.rssurl2.com match the rows in the db to the rows of the RSS feed, if it can't find a match for a particular item, delete it from the database.
Inserting new rows
The other problems I have is that each product has a wp_posts_id, which is associated to a wordpress post, so when a row is inserted it needs to take the wp_posts_id for that particular product, so if an item in the Hotpoint RSS feed is added, the wp_posts_id for the new row would need to be 12 along with the extracted data from the RSS feed.
I know this is quite complicated, but I'm hoping someone could possibly help or at least shed some light on how I'd go about doing this?
Kind Regards
I have a slight problem which I am struggling to come up with an idea/code for.
I am currently parsing RSS feeds and extracting out certain parts and then inserting these into a mysql database, which works well.
The problem comes when particular items are either added or removed from the RSS feed.
I need to do a script that, will detect such changes and either insert or delete from the database.
My MySQL tables is as follows:
This is basically a few rows of my table and you can see al the data in there.id | title | description | price | rss_url | wp_posts_id | delivery_cost | retailer_message | discount | total | logo_id
=====================================================================================
2 | Xbox 360 Halo Reach | Description text | 36.89 | http://www.rssurl1.com | 8 | On sale at Toys R Us | 0 | 36.89 | 0
=====================================================================================
3 | Halo: Reach | Description text here | 36.99 | http://www.rssurl1.com | 8 | On sale at Dixons | 0 | 36.99 | 0
=====================================================================================
4 | Halo : Reach (Xbox 360) | Description of product | 36.99 | http://www.rssurl1.com | 8 | On sale at Amazon | 0 | 36.99 | 0
=====================================================================================
16 | Hotpoint WMD962P | Another descriptio | 374.99 | http://www.rssurl2.com | 15 | 0.00 | On sale at Be Direct | 0 | 374.99 | 0
===========================================================================================
17 | HOTPOINT WMD962P | Description | 379.00 | http://www.rssurl2.com | 15 | 0.00 | On sale at Tribal UK | 18.95 | 360.05 | 4
What my script needs to do though is 2 parts, for example, is:
Deleting a row
If the row of id 17, is removed from the RSS feed, it needs deleting from the table. The only problem is though, the RSS feed doesn't have an id, so I am currently searching for the string 'On sale at XYZ' to do all of my work. So what the script needs to do is I guess loop through the rss_url of http://www.rssurl2.com match the rows in the db to the rows of the RSS feed, if it can't find a match for a particular item, delete it from the database.
Inserting new rows
The other problems I have is that each product has a wp_posts_id, which is associated to a wordpress post, so when a row is inserted it needs to take the wp_posts_id for that particular product, so if an item in the Hotpoint RSS feed is added, the wp_posts_id for the new row would need to be 12 along with the extracted data from the RSS feed.
I know this is quite complicated, but I'm hoping someone could possibly help or at least shed some light on how I'd go about doing this?
Kind Regards