Hi -

I am relatively new to 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 the relevant info and print
it to my browser with no problem using simpleXML for the parsing.
When I try to do an insert into mySQL it will only pull the first item
and date from each 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:

<?php

$con2 = mysql_connect("localhost","user", "password");
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)
{
$title = $item->title;
$titlequotes = "'".$title."'";
$pubDate = $item->pubDate;
$formattedDate = "'".date("Y-m-d H:i:s",
strtotime($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);



?>

Sarah wrote:
I believe this is more a php question and alt.php should have been a
better place, or alt.php.sql.


> I am relatively new to mySQL. I am trying to loop through a bunch of
> XML feeds, pull out some info and place it into a mySQL table.

If your php has mysqli, then use that instead of mysql, in this case I
think you could get better results.

I am
> able to loop through the feeds, pull out the relevant info and print
> it to my browser with no problem using simpleXML for the parsing.
> When I try to do an insert into mySQL it will only pull the first item
> and date from each 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:

Looking at the code, it should work, but there are things I would have
done in a different way (not necessarily affecting your original problem).

> <?php
>
> $con2 = mysql_connect("localhost","user", "password");
> if (!$con2)
> {
> die('Could not connect: ' . mysql_error());
> }
> mysql_select_db("Database", $con2);
>
> $entriesinsert = "0";

You use this to count up, then store a value instead of a string to
$entriesinsert,

$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) {
> $title = $item->title;
> $titlequotes = "'".$title."'";
over kill to use new variables, why not add the quotes at the query instead?

> $pubDate = $item->pubDate;
> $formattedDate = "'".date("Y-m-d H:i:s",strtotime($pubDate))."'";
over kill to use a new variable, why not add the quotes at the query
instead?
$formattedDate = date("Y-m-d H:i:s",strtotime($item->pubDate));


> $myspaceidquotes = "'".$myspaceid."'";
over kill to use a new variable, why not add the quotes at the query
instead?

>
> $resultBlogs = mysql_query("SELECT friendid, title, pubDate FROM
> blogs WHERE friendid='{$myspaceid}' AND pubDate='{$formattedDate}' AND
> title='{$item->$title}'");
>
> if (mysql_num_rows($resultBlogs)=="0") {
> mysql_query("INSERT INTO blogs (friendid, title, pubDate)
> VALUES ('{$myspaceid}', '{$item->title}', '{$formattedDate}')");
> $affectedrows=mysql_affected_rows();
> if($affectedrows=="1") {
> $entriesinsert++;
> }
> }
> }
> }
>
> echo $entriesinsert." entries inserted. Done!";
>
> mysql_close($con2);
>
>
>
> ?>
>


--

//Aho

J.O. Aho wrote:
> Sarah wrote:
> I believe this is more a php question and alt.php should have been a
> better place, or alt.php.sql.
>

Or even better would be comp.lang.php.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thank you for advice. I am quite new to php as well. I just assumed
it was a problem specific to mySQL since I could get it to correctly
print to my browser by replacing the mySQL select/insert statments
with echo the variables. I have cleaned up my code as you mentioned
and still seem to be having the problem so I will post to the php
group as you suggests.

Sarah