Writing an RSS feed to MySQL using PHP

Question

Basically I need to create a PHP document that will read an RSS feed and the write the information to a MySQL document. However I have very little experience in this area and I was wondering if someone would be able to provide an example of how to do this? I was thinking about something that incorperated MagpieRSS, however the documentation still has me very much in the dark =)

Any help would be greatly appreciated

Answer 1

The easiest way to go would be to use cURL to get the info as XML from the rss url, and then use simplexml to turn the rss XML into a traversable object. Use Xpath to get the parts of the XML you want to store in the DB. Finally, move the data to the DB.

Example

Sorry, I was rushing out the door when I saw your question. I actually wrote a really simple script a week ago to do most of what you are talking about:

 //cURL to get RSS as XML
function get_rss($feed_url) {
    $feed_request = curl_init($feed_url);
    curl_setopt($feed_request, CURLOPT_RETURNTRANSFER, 1);
    $feed_xml = curl_exec($feed_request);
    curl_close($feed_request);

    return $feed_xml;
}

function rss2sql($xml, $sql) {
    //simplexml to convert XML to objects
    $rss_xml = simplexml_load_string($xml);
    //XPath to get an array of items in RSS
    $rss_items = $rss_xml -> xpath('//item');
    if(!$rss_items) {
     die("No Items In RSS Feed!");
    }
    else {
            //Loop through each item, convert to string, insert string to MySQL
     foreach($rss_items as $item) {
                        $item_array = array($item->title,$item->link,$item->guid,$item->description);
                        $item_sql = "(".implode(","$item_array).")";
                        $item_sql = $sql -> escape_string($item_sql);
                        $insert = "INSERT INTO rsstable VALUES('$item_sql');
                        $sql -> query($insert);
     }
    }
}

$sql = new mysqli("localhost", "my_user", "my_password", "world");
$rss_url = "http://example.org/rssfeed";
$rss_xml = get_rss($rss_url);

rss2sql($its_rss_alerts_xml, $sql);

Answer 2

Not sure precisely what you are aiming for but:

From the Magpie documentation: (See Usage Examples)

require_once 'rss_fetch.inc';

$url = 'http://magpie.sf.net/samples/imc.1-0.rdf';
$rss = fetch_rss($url);

echo "Site: ", $rss->channel['title'], "<br>";
foreach ($rss->items as $item ) {
    $title = $item[title];
    $url   = $item[link];
    echo "<a href=$url>$title</a></li><br>";
}

That will get the RSS feed into usable parts. You could change to something like this:

foreach ($rss->items as $item ) {
    $title = $item[title];
    $url   = $item[link];
    mysql_query("INSERT INTO `table` (`id`, `title`, `link`) VALUES (NULL, '$title', '$url')")";
}

That should get you started if nothing else.

Answer 3

MagpieRSS seems to be a good choice. Yes, the documentation could be better but you have all of the examples you need on the front page:

require('rss_fetch.inc');
$rss = fetch_rss($url);
$items = rss->items;

With just that you have an associative array which you can then manipulate to insert into your database.

foreach ($rss->items as $item ) {
    $title = $item[title];
    $url   = $item[link];
    mysql_query("INSERT INTO rss_articles ( title, link ) VALUES ( $title, $url );

    //Of course, this has no security measures which you should really look into.     
}