PHP XML MySQL

[timmah1]

I thought I had this working, but I was wrong.

I need to take an xml feed and insert it into a database.

 

I can grab the contents of the xml, this is how it comes to me:

XML

http://www.novafantasysports.com/xml/bbfpbasketballplayernews/node_feed/?User=bbfreepicks_nbaplayernews&pw=bbfpFFBP

<?xml version="1.0" encoding="ISO-8859-1"?>

<nodes>
<article id="11142">
<title>Cavs want Powe</title>
<author>Geno_Nevertegi</author>
<created>2009-08-02 21:38:32</created>
<content><p>
<b>Update:</b> After failing to land Hakim Warrick, the Cavaliers have reportedly offered a contract to  Leon Powe (knee), according to the <a href="http://www.cleveland.com/cavs/plaindealer/index.ssf?/base/sports/1249115540305490.xml&amp;coll=2" target="_blank">Cleveland Plain Dealer</a>. Cleveland is Powe&#39;s top choice to sign with, so it appears as if a deal could be imminent. 
</p>
<p>
&nbsp;
</p>
<p>
<b>Fantasy Impact: </b>Powe is a guy who will not be ready to begin the season, and there are a number of questions marks pertaining to his return.  His target is early 2010, but the big question being how effective he would be able to be.  Powe might be a nice mid-season addition to fantasy rosters.  
</p>
<p>
&nbsp;
</p>
</content>
</article>
</nodes>

 

Here is the PHP code I'm trying to use to insert it into the database

PHP

<?php
$final = array(file_get_contents('http://www.novafantasysports.com/xml/bbfpbasketballplayernews/node_feed/?User=bbfreepicks_nbaplayernews&pw=bbfpFFBP'));

$query = "INSERT INTO feeds(article, title, author, created, content) VALUES";

for($i = 0, $c = count($final); $i < $c; $i += 3) {
$query .= "('{$final[$i]}', '{$final[$i + 1]}', '{$final[$i + 2]}', '{$final[$i + 3]}', '{$final[$i + 4]}')";

if($i + 3 < $c) {
$query .= ", ";
}
}

$RESULT = mysql_query($query);

if(!!$RESULT) {
echo "Query was successfull";
echo "<blockquote>". print_r($final) ."</blockquote>";
}
else {
echo "Query failed
<blockquote>". $query ."</blockquote>
<blockquote>". mysql_error($DB) ."</blockquote>";} 
?>

 

Now that inserts nothing into the database.

 

But if I do this

<?php
$final = array(file_get_contents('http://www.novafantasysports.com/xml/bbfpbasketballplayernews/node_feed/?User=bbfreepicks_nbaplayernews&pw=bbfpFFBP'));

$query = "INSERT INTO feeds(val) VALUES";

for($i = 0, $c = count($final); $i < $c; $i += 3) {
$query .= "('{$final[$i]}')";

if($i + 3 < $c) {
$query .= ", ";
}
}

$RESULT = mysql_query($query);

if(!!$RESULT) {
echo "Query was successfull";
echo "<blockquote>". print_r($final) ."</blockquote>";
}
else {
echo "Query failed
<blockquote>". $query ."</blockquote>
<blockquote>". mysql_error($DB) ."</blockquote>";} 
?>

I get the info inserted into the database, but it's all in one. I would like to be able to insert each item into a row by itself

article_id

title

author

created

content

 

I hope this makes sense

 

How can I go about doing this?

 

Thanks in advance

[timmah1]

bump

[timmah1]

Anybody??

[timmah1]

ok, I almost have this working, but now I'm getting this error

Query failed

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Knicks claim Jason Williams', '

    \nUpdate: The Knicks have the exclusi' at line 1

 

Can anybody please see what the problem might be???

 

<?php
$xml=("http://www.novafantasysports.com/xml/bbfpbasketballplayernews/node_feed/?User=bbfreepicks_nbaplayernews&pw=bbfpFFBP");

$xmlDoc = new DOMDocument();
$xmlDoc->load($xml);

$db = mysql_connect("localhost", "xxxxx", "xxxxx");
mysql_select_db("xxxxx", $db);

//get and output "<item>" elements
$x=$xmlDoc->getElementsByTagName('article');

$sql = "INSERT INTO feeds(article, title, author, created, content) VALUES";
//for ($i = 0; $i < $x; $i++) //{
for ($i=0; $i<=19; $i++)
  {
  $item_title=$x->item($i)->getElementsByTagName('title')
  ->item(0)->childNodes->item(0)->nodeValue;
  $item_desc=$x->item($i)->getElementsByTagName('content')
  ->item(0)->childNodes->item(0)->nodeValue;
  
$sql .= "('".mysql_real_escape_string($item_title)."', '".mysql_real_escape_string($item_desc)."')";
}
$result = mysql_query($sql);
if(!!$RESULT) {
echo "Query was successfull";
  echo ("<p><a href='" . $item_link
  . "'>" . $item_title . "</a>");
  echo ("<br />");
  echo ($item_desc . "</p>");
  }
else {
echo "Query failed
<blockquote>". mysql_error($db) ."</blockquote>";
}

?> 

[venturemc]

I'm having similar issues and am a PHP mySQL newb. (PHP 5.3 / mySQL5.137)

 

The error you're getting looks very similar to mine. Do you have a quote the string you're trying to insert?

 

That needs to be handled and I can't for the life of me figure out the syntax to get the damn single quote into the text field via PHP. Hopefully that's your issue and somebody can help us both!

[timmah1]

venturemc,

 

Here is the code that I got working

<?php
$xml=("http://www.novafantasysports.com/xml/bbfpbasketballplayernews/node_feed/?User=bbfreepicks_nbaplayernews&pw=bbfpFFBP");

$xmlDoc = new DOMDocument();
$xmlDoc->load($xml);

$x=$xmlDoc->getElementsByTagName('article');

$sql = "INSERT INTO nba_feeds(title, author, content, date_posted, uploaded) VALUES";

for ($i=0; $i<=19; $i++)
  {
  $item_title=$x->item($i)->getElementsByTagName('title')
  ->item(0)->childNodes->item(0)->nodeValue;
  
   $item_author=$x->item($i)->getElementsByTagName('author')
  ->item(0)->childNodes->item(0)->nodeValue;
  
  $item_created=$x->item($i)->getElementsByTagName('created')
  ->item(0)->childNodes->item(0)->nodeValue;
  
  $item_desc=$x->item($i)->getElementsByTagName('content')
  ->item(0)->childNodes->item(0)->nodeValue;
$sql .= "('" . mysql_real_escape_string($item_title). "', '". mysql_real_escape_string($item_author) . "', '". mysql_real_escape_string($item_desc) . "', '". mysql_real_escape_string($item_created) . "', '" . date("Y-m-d H:i:s") . "')"; 

if($i < 19) {
$sql .= ", ";
}
else {
$sql .=";";
}
  }  
$result = mysql_query($sql);
?>

[DavidAM]

That error message ("You have an error in your SQL syntax; ...") indicates that the SQL you built is not valid.  Whenever I get that message, I add an echo statement immediately before executing the sql:

 

echo $sql;
$result = mysql_query($sql);

 

Then I look over the sql and hunt for the extract it shows in the error message, and then look around there for the problem with the statement.  The error is not always at the exact text the error message indicates, sometimes a missing comma or quote mark earlier in the text will cause the message to display the wrong spot as the error.  If I can't find the error, I will cut and paste the sql into the MySQL Query Browser (since it colors the code it might help) to see if I can find the error.  If that doesn't work, try executing it and cutting out portions of it until you slap yourself in the head and say "how stupid can I be to miss that?!?!?" (at least, that's what I always end up doing).

 

If you want to post the sql after you echo it, we can look at it and see if we can help find the problem.  I don't see any obvious logic errors in the way you are building it.

[venturemc]

Thanks, I figured out my issue.

 

It wasn't the syntax of my INSERT statent. Rather it was an issue with the loop I used to process the input values and my reference of those values in the INSERT statement.

 

Although the variable looked properly formatted when it was posted tothe screen, the original input value (not the processed value) was being written into the statement.