XML to mySQL - Loop issue?

Hi -

I am relatively new to PHP and mySQL. I am trying to loop through a
bunch of
XML feeds, pull out some info and place it into a mySQL table. I am
able to loop through the feeds, pull out all the relevant info and
print
it to my browser with no problem using simpleXML for the parsing.
When I try inserting the variables into mySQL instead of printing the
variables to the browser it will only pull the first item and date
from each XML page instead of all of the data. I have no idea
why this would be. Any ideas? Here is the code I have so far:

$con2 = mysql_connect("localhost","USER", "PASS");
if (!$con2)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("DATABASE", $con2);


$entriesinsert = 0;


$resultBlogsMarket = mysql_query("SELECT * FROM myspacepages");


while($rowMarket = mysql_fetch_array($resultBlogsMarket))
{
$myspaceid = $rowMarket['friendid'];
$myspacemarket = $rowMarket['market'];
$myspaceblogURL = "http://blog.myspace.com/blog/rss.cfm?friendID=".
$myspaceid;


// Load and parse the XML document
$rss = simplexml_load_file($myspaceblogURL);


// Here we'll put a loop to include each item's title and date
foreach ($rss->channel->item as $item)
{
$titlequotes = "'".$item->title."'";
$formattedDate = "'".date("Y-m-d H:i:s", strtotime($item-
>pubDate))."'";
$myspaceidquotes = "'".$myspaceid."'";


$resultBlogs = mysql_query("SELECT friendid, title, pubDate FROM
blogs WHERE friendid=$myspaceidquotes AND pubDate=$formattedDate AND
title=$titlequotes");


if (mysql_num_rows($resultBlogs)=="0")
{
mysql_query("INSERT INTO blogs (friendid, title, pubDate)
VALUES ($myspaceidquotes, $titlequotes, $formattedDate)");
$affectedrows=mysql_affected_rows();
if($affectedrows=="1")
{
$entriesinsert++;
}
}
}
}


echo $entriesinsert." entries inserted. Done!";

mysql_close($con2);
?>

Re: XML to mySQL - Loop issue?

On Sun, 28 Oct 2007 19:15:32 +0100, Sarah wrote:

> Hi -
>
> I am relatively new to PHP and mySQL. I am trying to loop through a
> bunch of
> XML feeds, pull out some info and place it into a mySQL table. I am
> able to loop through the feeds, pull out all the relevant info and
> print
> it to my browser with no problem using simpleXML for the parsing.
> When I try inserting the variables into mySQL instead of printing the
> variables to the browser it will only pull the first item and date
> from each XML page instead of all of the data. I have no idea
> why this would be. Any ideas? Here is the code I have so far:

While developing, check mysql_error() for any failed queries. And escape
your data properly with mysql_real_escape_string before using it in a
query. If the same loop prints the data correctly, check what your if
statements and your insert query actually do.
--
Rik Wasmus

Re: XML to mySQL - Loop issue?

On Oct 28, 7:15 pm, Sarah wrote:
> Hi -
>
> I am relatively new to PHP and mySQL. I am trying to loop through a
> bunch of
> XML feeds, pull out some info and place it into a mySQL table. I am
> able to loop through the feeds, pull out all the relevant info and
> print
> it to my browser with no problem using simpleXML for the parsing.
> When I try inserting the variables into mySQL instead of printing the
> variables to the browser it will only pull the first item and date
> from each XML page instead of all of the data. I have no idea
> why this would be. Any ideas? Here is the code I have so far:
>
> > $con2 = mysql_connect("localhost","USER", "PASS");
> if (!$con2)
> {
> die('Could not connect: ' . mysql_error());
> }
> mysql_select_db("DATABASE", $con2);
>
> $entriesinsert = 0;
>
> $resultBlogsMarket = mysql_query("SELECT * FROM myspacepages");
>
> while($rowMarket = mysql_fetch_array($resultBlogsMarket))
> {
> $myspaceid = $rowMarket['friendid'];
> $myspacemarket = $rowMarket['market'];
> $myspaceblogURL = "http://blog.myspace.com/blog/rss.cfm?friendID=".
> $myspaceid;
>
> // Load and parse the XML document
> $rss = simplexml_load_file($myspaceblogURL);
>
> // Here we'll put a loop to include each item's title and date
> foreach ($rss->channel->item as $item)
> {
> $titlequotes = "'".$item->title."'";
> $formattedDate = "'".date("Y-m-d H:i:s", strtotime($item->pubDate))."'";
>
> $myspaceidquotes = "'".$myspaceid."'";
>
> $resultBlogs = mysql_query("SELECT friendid, title, pubDate FROM
> blogs WHERE friendid=$myspaceidquotes AND pubDate=$formattedDate AND
> title=$titlequotes");
>
> if (mysql_num_rows($resultBlogs)=="0")
> {
> mysql_query("INSERT INTO blogs (friendid, title, pubDate)
> VALUES ($myspaceidquotes, $titlequotes, $formattedDate)");
> $affectedrows=mysql_affected_rows();
> if($affectedrows=="1")
> {
> $entriesinsert++;
> }
> }
> }
> }
>
> echo $entriesinsert." entries inserted. Done!";
>
> mysql_close($con2);
> ?>

I have the feeling it's because mysql_num_rows($resultBlogs)=="0" is
resolving to true after each first iteration.

This might be because:
"SELECT friendid, title, pubDate FROM
blogs WHERE friendid=$myspaceidquotes AND pubDate=$formattedDate AND
title=$titlequotes"
is the same on each iteration.

Try to print it out to the browser for each iteration. If it's the
same for all iterations, then most probably your query's logic is
wrong. Another possibility is that these values are not being updated
correctly: $myspaceidquotes, $formattedDate, $titlequotes
Print them to the browser and see if it's what you expect.

Additional Note: Good practice is to free your mysql results once
you're finished with them, add:
mysql_free_result($resultBlogsMarket) after the end of the outer loop.
mysql_free_result($resultBlogs) before the end of the inner loop.

Re: XML to mySQL - Loop issue?

Man do I feel stupid. In messing around earlier with the mySQL table
I inadvertently deleted my primary key column and made my friendid
column my primary key. This inadvertently limited my entries to one
per friendid instead of all of the data. I can't believe what a dumb
mistake that was. I swear I am usually more careful!

Once I added back in the column and got the primary key straightened
out I then was missing about 5 entries and wasn't quite sure why since
the loops were working. In reviewing your responses and looking back
over the data it looks to be because I had not been using
mysql_real_escape_string. As soon as I used that my last 5 missing
entries were able to be inserted with no problem.

I will also fix my code to add in (I appreciate the best practices
tip!):
mysql_free_result($resultBlogsMarket) after the end of the outer
loop.
mysql_free_result($resultBlogs) before the end of the inner loop.


Thank you so much for your help and patience with me. I really
appreciate it!!


Sarah