CODEWITHSUNDEEP
phpmysqlrss
James McLaren
James McLaren

Reputation:

Writing an RSS feed to MySQL using PHP

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

Upvotes: 2

Views: 4431

Answers (3)

Anthony
Anthony

Reputation: 37065

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 {
        // Create Prepared Statement for Insert and bind variables.
        $item_insert = $sql->prepare("INSERT INTO rsstable VALUES (?, ?, ?, ?)");
        $item_insert->bind_param('ssss', $item->title,$item->link,$item->guid,$item->description);
        //Loop through each item and execute prepared statement
        foreach($rss_items as $item) {
            $item_insert->execute();
        }
    }
}

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

rss2sql($rss_xml, $sql);

Upvotes: 4

Daniel Sorichetti
Daniel Sorichetti

Reputation: 1951

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.       
}

Upvotes: 0

Jason
Jason

Reputation: 15335

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.

Upvotes: 6

Related Questions