[SOLVED] RSS to MySQL

[fredbear]

Let me preface this by saying that I'm still a PHP noob.  I'm fairly decent in SQL, but still learning PHP and using the two together.  What I'm trying to do is take an RSS feed, parse it (using Magpie RSS), and then dump it into a MySQL database.  What I get from Magpie is this: 

 

MagpieRSS Object
(
    [parser] => Resource id #9
    [current_item] => Array
        (
        )

    [items] => Array
        (
            [0] => Array
                (
                    [title] => Set-Up Volunteers Needed For New York Countdown To A Cure
                    [link] => http://myproject.serve.gov/public/OpportunityDetail.aspx?projectId=10019&&subProjectId=4831715#7b1b7ae2346c4f0fd7d5dfe7db89d6d0
                    [description] => Help is needed for the NephCure Foundations 2009 New York Countdown to a Cure!! 10 volunteers are needed to help with set-up for this charity event. Volunteers should arrive at 1pm to begin set-up and will be fed. Countdown to a Cure will be held at the Citi Field Caesars Club in Flushing, New York.
                    [guid] => http://myproject.serve.gov/public/OpportunityDetail.aspx?projectId=10019&&subProjectId=4831715#7b1b7ae2346c4f0fd7d5dfe7db89d6d0
                    [fp] => Array
                        (
                            [id] => 7b1b7ae2346c4f0fd7d5dfe7db89d6d0
                            [groupid] => Mc271d9321ce63efeb31d3ef2bc718962
                            [provider] => myproj_servegov
                            [startdate] => 2009-11-12 00:00:00
                            [enddate] => 2009-11-13 00:00:00
                            [base_url] => 7b1b7ae2346c4f0fd7d5dfe7db89d6d0
                            [xml_url] => http://myproject.serve.gov/public/OpportunityDetail.aspx?projectId=10019&&subProjectId=4831715#7b1b7ae2346c4f0fd7d5dfe7db89d6d0
                            [url_short] => myproject.serve.gov
                            [latlong] => 37.09024,-95.712891
                            [location_name] => United States
                            [interest_count] => 0
                            [impressions] => 0
                            [quality_score] => 0.1
                            [categories] => Health
                            [sponsoringorganizationname] => The NephCure Foundation
                            [openended] => False
                            [starttime] => 0
                            [endtime] => 0
                            [contactemail] => mlong@nephcure.org
                        )

                    [summary] => Help is needed for the NephCure Foundations 2009 New York Countdown to a Cure!! 10 volunteers are needed to help with set-up for this charity event. Volunteers should arrive at 1pm to begin set-up and will be fed. Countdown to a Cure will be held at the Citi Field Caesars Club in Flushing, New York.
                )

 

Basically what it looks like to me is that there is an array within an array.  I can access stuff within the items array (title, link, desc, etc) but anything within the fp array within the items array I cannot seem to figure out how to access (startdate, enddate, location, email, etc).  Here is what I have so far for my insert code:

 

<?php include('rss_fetch.inc');

// Set error reporting for this 
error_reporting(E_ERROR);

// Fetch RSS feed 
$rss = fetch_rss('feed url');
if ($rss) {
// Split the array to show first 5  
$items = array_slice($rss->items,0,10);
// Cycle through each item 
foreach ($items as $item )
{   
$con = mysql_connect("connection info);
if (!$con)
  {
  die('MySQL could not connect: ' . mysql_error());
  }
  
mysql_select_db("rsstomysql", $con);

$sql="INSERT INTO opportunities (opp_id, opp_title, opp_link, opp_desc, opp_provider, opp_startdate, opp_enddate, opp_location_name, opp_categories, opp_openended, opp_sponsororg, opp_starttime, opp_endtime, opp_contactemail, opp_contactphone, opp_skills)
VALUES(".$item['`id`']."`, `".$item['`title`']."`, `".$item['`link`']."`, `".$item['`description`']."`, `".$item['`provider`']."`, `".$item['`startdate`']."`, `".$item['`enddate`']."`, `".$item['`location_name`']."`, `".$item['`categories`']."`, `".$item['`openended`']."`, `".$item['`sponsoringorganizationname`']."`, `".$item['`starttime`']."`, `".$item['`endtime`']."`, `".$item['`contactemail`']."`, `".$item['`contactphone`']."`, `".$item['`skills`']." ) ";
if (!mysql_query($sql,$con))
  {
  die('MySQL Error: ' .mysql_errno() .mysql_error());
  }
echo "Records added";
mysql_close($con);
}
}
else
{
  echo '<h2>Magpie Error:</h2><p>'.magpie_error().'</p>';
}
// Restore original error reporting value
@ini_restore('error_reporting');
?> 

 

Anyone out there know how to access that second array?  Thanks in advance!

[cags]

You should be able to use either...

 

<?php
echo $item['fp']['id'];
echo $item['fp']['goupid'];
// etc, etc..

foreach($item['fp'] as $k=>$sub_item) {
  echo $k . " = " . $sub_item . "<br/>";
}
?>

 

Note: obviously I just echo'd out the values, the idea was to just show you how to access the elements. You can use these values to insert into the database.

[fredbear]

Ah thank you, it seems so obvious now.  Thanks again! 

[fredbear]

I actually am having a different issue with this now.  It seems the fields from the RSS feed aren't very consistent.  They always have the same names, but are not always there.  For example in one post there may be a field for "contactemail" and then in another that field may not be there.  I think that is what is not causing me to get this when I try to run the script:  MySQL Error: 1136Column count doesn't match value count at row 1

 

I've checked to make sure I'm not missing any commas and all of my columns in my DB match up to what I have on the script, so I'm at a little bit of a loss.  Anyone know how I could basically tell MySQL that if the field isn't there to skip it (or add a NULL value)?  Thanks in advance!

[cags]

Yer, most of the nodes in RSS are not required, I found this out when making a dynamic RSS generator and was checking out the specs for RSS 2.0. Is your SQL still the same as it was before? You have 16 entries in both columns and your VALUES sections. So you shouldn't get that error...

[fredbear]

  It's very strange.  Just as a test I cut it down to three columns I know are always presented by the RSS feed:  title, link, and description.  It still gave me the error. 

 

Here's my shortened insert code:

 

<?php include('rss_fetch.inc');

// Set error reporting for this 
error_reporting(E_ERROR);

// Fetch RSS feed 
$rss = fetch_rss('feed url');
if ($rss) {
// Split the array to show first 5  
$items = array_slice($rss->items,0,10);
// Cycle through each item
foreach ($items as $item )
{   
$con = mysql_connect("connection info");
if (!$con)
  {
  die('MySQL could not connect: ' . mysql_error());
  }
  
mysql_select_db("rsstomysql", $con);

$sql="INSERT INTO opportunities (opp_title, opp_link, opp_desc)
VALUES(".$item['`title`']."`, `".$item['`link`']."`, `".$item['`description`']." ) ";
if (!mysql_query($sql,$con))
  {
  die('MySQL Error: ' .mysql_errno() .mysql_error());
  }
echo "Records added";
mysql_close($con);
}
}
else
{
  echo '<h2>Magpie Error:</h2><p>'.magpie_error().'</p>';
}
// Restore original error reporting value
@ini_restore('error_reporting');
?> 

 

Just for fun, here's my table create script:

 

CREATE TABLE opportunities(
opp_title VARCHAR(250),
opp_link VARCHAR(250),
opp_desc LONGTEXT);

[cags]

Your problem is quite probably caused by backticks and a single quotes.

 

You have...

 

<?php
$sql="INSERT INTO opportunities (opp_title, opp_link, opp_desc)
VALUES(".$item['`title`']."`, `".$item['`link`']."`, `".$item['`description`']." ) ";
?>

 

Try this...

 

<?php
$sql="INSERT INTO `opportunities` (`opp_title`, `opp_link`, `opp_desc`)
VALUES('".$item['title']."', '".$item['link']."', '".$item['description']."') ";
?>

 

Fieldnames can be surrounded by back ticks (it's not required but makes code easier to read), any value being entered into a database (unless it's an Integer) must be surrounded by single quote marks. To access a key in an array you should include single quotes (or double quotes, but unless your using a $variable your better sticking to single).

[fredbear]

Ahh, thank you!  That works much better.