[SOLVED] Insert ARRAY into database

[timmah1]

I can print out the array with no problem, but now I want to be be able to insert the info in to a database.

 

Here is the code I have so far

<?php
function xml2ary(&$string) {
    $parser = xml_parser_create();
    xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
    xml_parse_into_struct($parser, $string, $vals, $index);
    xml_parser_free($parser);

    $mnary=array();
    $ary=&$mnary;
    foreach ($vals as $r) {
        $t=$r['tag'];
        if ($r['type']=='open') {
            if (isset($ary[$t])) {
                if (isset($ary[$t][0])) $ary[$t][]=array(); else $ary[$t]=array($ary[$t], array());
                $cv=&$ary[$t][count($ary[$t])-1];
            } else $cv=&$ary[$t];
            if (isset($r['attributes'])) {foreach ($r['attributes'] as $k=>$v) $cv['_a'][$k]=$v;}
            $cv['_c']=array();
            $cv['_c']['_p']=&$ary;
            $ary=&$cv['_c'];

        } elseif ($r['type']=='complete') {
            if (isset($ary[$t])) { // same as open
                if (isset($ary[$t][0])) $ary[$t][]=array(); else $ary[$t]=array($ary[$t], array());
                $cv=&$ary[$t][count($ary[$t])-1];
            } else $cv=&$ary[$t];
            if (isset($r['attributes'])) {foreach ($r['attributes'] as $k=>$v) $cv['_a'][$k]=$v;}
            $cv['_v']=(isset($r['value']) ? $r['value'] : '');

        } elseif ($r['type']=='close') {
            $ary=&$ary['_p'];
        }
    }    
    
    _del_p($mnary);
    return $mnary;
}

function _del_p(&$ary) {
    foreach ($ary as $k=>$v) {
        if ($k==='_p') unset($ary[$k]);
        elseif (is_array($ary[$k])) _del_p($ary[$k]);
    }
}

function ary2xml($cary, $d=0, $forcetag='') {
    $res=array();
    foreach ($cary as $tag=>$r) {
        if (isset($r[0])) {
            $res[]=ary2xml($r, $d, $tag);
        } else {
            if ($forcetag) $tag=$forcetag;
            $sp=str_repeat("\t", $d);
            $res[]="$sp<$tag";
            if (isset($r['_a'])) {foreach ($r['_a'] as $at=>$av) $res[]=" $at=\"$av\"";}
            $res[]=">".((isset($r['_c'])) ? "\n" : '');
            if (isset($r['_c'])) $res[]=ary2xml($r['_c'], $d+1);
            elseif (isset($r['_v'])) $res[]=$r['_v'];
            $res[]=(isset($r['_c']) ? $sp : '')."</$tag>\n";
        }
        
    }
    return implode('', $res);
}

// Insert element into array
function ins2ary(&$ary, $element, $pos) {
    $ar1=array_slice($ary, 0, $pos); $ar1[]=$element;
    $ary=array_merge($ar1, array_slice($ary, $pos));
}
//print_r ($xml);

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

$final = $xml;
// Connect to the database
$DB = mysql_connect("localhost", "basket", "1q2w3e4r");
mysql_select_db("basket_main", $DB);

// Create the qyery string
$query = "INSERT INTO feeds(id, name, val) VALUES";

// Loop through the array
for($i = 0, $c = count($final); $i < $c; $i += 3) {
// Add the next batch of values to the query string
$query .= "({$final[$i]}, '{$final[$i + 1]}', '{$final[$i + 2]}')";

// Add a comma is this is not the last batch
if($i + 3 < $c) {
$query .= ", ";
}
}

// Execute the query
$RESULT = mysql_query($query);

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

 

Right now, I keep getting

Query Failed
    INSERT INTO feeds(id, name, val) VALUES(, '', '')

    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 ' '', '')' at line 1

 

Can anybody see that the problem is?

 

Thanks in advance

[_DarkLink_]

A syntax error is like writing a word, if you are missing a letter, it is not that word anymore.

 

You seem to be missing a semi-colon at the end of your SQL query. (';')

 

Try replacing:

// Add the next batch of values to the query string
$query .= "({$final[$i]}, '{$final[$i + 1]}', '{$final[$i + 2]}')";

 

With:

// Add the next batch of values to the query string
$query .= "({$final[$i]}, '{$final[$i + 1]}', '{$final[$i + 2]}');";

 

Hope it helps.

[Catfish]

Query Failed

    INSERT INTO feeds(id, name, val) VALUES(, '', '')

 

not sure, but if the value of $final[$i] is empty (ie: not integer) then you will need to change:

 

// Add the next batch of values to the query string

$query .= "({$final[$i]}, '{$final[$i + 1]}', '{$final[$i + 2]}')";

 

to:

 

// Add the next batch of values to the query string

$query .= "('{$final[$i]}', '{$final[$i + 1]}', '{$final[$i + 2]}')";

 

to ensure that you have 3 sets of single quotes in the SQL INSERT statement.

 

I'm not sure this is the problem, but that's what I'd be trying.

[timmah1]

Thank you catfish, that did it.

 

Now, the problem I'm having is that it is only inserting blank rows, but only 1

 

Here is the modified version

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

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

for($i = 0, $c = count($final); $i < $c; $i += 3) {
// Add the next batch of values to the query string
$query .= "('{$final[$i]}', '{$final[$i + 1]}', '{$final[$i + 2]}')";

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>";} 
?>

 

The query is successfull, but nothing is being inserted.

Here is what prints

Query was successfull
Array ( [0] => Geno_Nevertegi 2009-08-02 21:38:32 <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&coll=2" target="_blank">Cleveland Plain Dealer</a>. Cleveland is Powe's top choice to sign with, so it appears as if a deal could be imminent. </p> <p>   </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>   </p> Geno_Nevertegi 2009-08-01 13:00:23 <p> <b>Update:</b> The Knicks have the exclusive rights to negotiate with free agent point guard Jason Williams, the <a href="http://www.nypost.com/seven/08012009/sports/knicks/knicks_to_talk_to_j_will_182445.htm" target="_blank">New York Post</a> explains. That right runs through next Thursday. A team spokesman confirms that the Knicks will talk with Williams next week. The 33-year-old guard retired last September. </p> <p>   </p> <p> <b>Fantasy Impact: </b> The Knicks were awarded the negotiating rights because of their record last season.  Williams may end up in New York, but he has no fantasy value this coming season. </p> Geno_Nevertegi 2009-07-31 21:56:35 <p> <b>Update:</b> Gabe Pruitt has been waived by the Celtics, according to <a href="http://sports.espn.go.com/nba/news/story?id=4370109&campaign=rss&source=NBAHeadlines" target="_blank">ESPN</a>. </p> <p>   </p> <p> <b>Fantasy Impact: </b> Pruitt saw work in just 47 games last year, and managed to shoot at just a 30.7% clip.  That wasn't enough to stick around in Beantown.  He may find other work, but his fantasy value is minimal at best. </p> Geno_Nevertegi 2009-07-31 21:53:09 <p> <b>Update:</b> Bruce Bowen has been released by the Bucks, according to <a href="http://sports.espn.go.com/nba/news/story?id=4370109&campaign=rss&source=NBAHeadlines" target="_blank">ESPN</a>. </p> <p>   </p> <p> <b>Fantasy Impact:</b> The team saves themselves $2M by waiving the 38 year old veteran.  He managed to average just 2.7ppg over an 80 game haul last year.  He has no fantasy value, although he should latch on with another team. </p> Geno_Nevertegi 2009-07-31 16:33:48 <p> <b>Update:</b> <a href="http://sports.espn.go.com/nba/news/story?id=4369069" target="_blank">ESPN.com</a> reports that Tyler Hansbrough could miss up to two months due to a right shin injury. The Pacers maintain that the power forward will be available at the start of the season. </p> <p>   </p> <p> <b>Fantasy Impact: </b> Hansbrough had a stress fracture in the shin last year which caused him to miss time for the Heels, and it appears this is a related injury.  Folks hoping to select Hansbrough in deep leagues or rookie leagues should be thankful this came out now because this injury would only get worse over the course of the long NBA season. </p> jsavino 2009-07-31 11:34:36 <p> <b>Update: </b><a href="http://sports.espn.go.com/nba/columns/story?columnist=stein_marc&page=Chatter-090730" target="_blank">ESPN.com</a> reports that free-agent forward Hakim Warrick is believed to be deciding among the Bucks, Cavaliers, and 76ers. </p> <p>   </p> <p> <b>Fantasy Impact: </b>Warrick lost playing time last year to Darrell Arthur, meaning that Warrick's days were numbered in Memphis.  Warrick is a good defender, who has averaged 10.2 points on 50 percent shooting over his four year career and would be a nice addition to any team. </p> jsavino 2009-07-31 11:05:12 <p> <b>Update:</b> Knicks president Donnie Walsh met with Allen Iverson's agent, Leon Rose, two weeks ago, according to the <a href="http://www.realgm.com/src_wiretap_archives/60901/20090731/source_knicks_met_with_iverson/" target="_blank">New York Daily News. </a>The Knicks are believed to want Ramon Sessions foremost, but if theycontinue to lose out on free agents, they could revisit adding Iverson to their squad. </p> <p>   </p> <p> <b>Fantasy Impact</b><b>: </b> Iverson could help to jump start the revival of the Knicks, assuming Mike D'Antoni would use him as a starter. D'AntonI has never excelled with guys who have major egos (see Marbury and Curry) so chances  are he isn't pushing for the team to ink Iverson. </p> jsavino 2009-07-31 11:00:06 <p> Update: <a href="http://sports.espn.go.com/nba/columns/story?columnist=stein_marc&page=Chatter-090730" target="_blank">ESPN.com</a> reports that the Knicks will offer restricted free agent Ramon Sessions a contract soon. The Bucks will have an opportunity to match, but the Knicks feel as if they'll be able to land him. </p> <p> <br /> Fantasy Impact<b>: </b> The Bucks feel that re-signing Sessions would impact 10th overall selection, Brandon Jennings' development.  Sessions' fantasy value will increase if he lands in Mike D'Antoni's offense. </p> jsavino 2009-07-30 09:26:38 <p> <b>Update: </b>Mike Dunleavy (hip, knee) believes he'll be back before his scheduled January return date, according to the <a href="http://blogs.indystar.com/pacersinsider/archives/2009/07/the_pressure_is.html" target="_blank">Indianapolis Star</a>. </p> <p>   </p> <p>   </p> <p> <b>Fantasy Impact</b><b>: </b> Dunleavy put up 15ppg in just 18 games last year.  Watch out for him during the season, he would be a nice late season addition to fantasy rosters. </p> <p>   </p> Geno_Nevertegi 2009-07-29 19:59:58 <p> <b>Update: </b> Marcus Williams is believed to be nearing a one-year deal with the Memphis Grizzlies, according to the <a href="http://www.commercialappeal.com/news/2009/jul/29/point-guard-marcus-williams-close-signing-deal-mem/?partner=RSS" target="_blank">Memphis Commercial-Appeal</a>. </p> <p>   </p> <p>   </p> <p> <b>Fantasy Impact: </b> Williams hasn't panned out so far in his first three seasons in the league, and was even released by Golden State last year.  He did have a nice summer league, and will most likely serve as Mike Conley's backup.  Pass on him on draft day. </p> Geno_Nevertegi 2009-07-29 19:55:04 <p> <b>Update:</b> The Atlanta Hawks have resigned Marvin Williams with a five year, $40 million deal, according to <a href="http://sports.yahoo.com/nba/news?slug=mc-williams072909&prov=yhoo&type=lgns" target="_blank">Yahoo! Sports</a>. </p> <p>   </p> <p>   </p> <p>   </p> <p> <b>Fantasy Impact: </b> Williams hasn't developed in the NBA as quickly as the team has wanted.  He is still a young guy and he has posted 13 ppg the past three years, and he's a solid to mid late round draft pick in fantasy leagues.   </p> <p>   </p> Geno_Nevertegi 2009-07-29 19:18:36 <p> <b>Update: </b>The Grizzlies have signed Sam Young, their second round draft pick from last month, according to <a href="http://www.miamiherald.com/sports/basketball/wires/story/1162826.html" target="_blank">The Sports Network</a>. <br /> <b></b> </p> <p>   </p> <p>   </p> <p> <b>Fantasy Impact: </b> The Grizzlies stole Sam Young in the draft, and he went on to wow the team in summer league play posting 13.6 points and 4.2 rebounds per game.  He is a solid addition to the team, but won't have much fantasy value this season.  </p> <b></b> jsavino 2009-07-29 16:54:08 <p> <b>Update:</b> The <a href="http://www.nola.com/hornets/index.ssf/2009/07/new_orleans_hornets_sign_free_1.html" target="_blank">New Orleans Times-Picayune</a> reports that the Hornets have signed big man Ike Diogu. </p> <p>   </p> <p>   </p> <p> <b>Fantasy Impact: </b>Diogu has bounced around with four different teams since being selected in the top 10 of the 2005 draft.  He has some talent, but he just doesn't make the impact teams are hoping for.  He once again will have no fantasy value. </p> jsavino 2009-07-29 13:00:02 <p> <b>Update:</b> According to the <a href="http://www.boston.com/sports/basketball/celtics/articles/2009/07/29/guard_gabe_pruitts_time_with_celtics_may_be_nearing_an_end/?rss_id=Boston.com+--+Boston+Celtics+news" target="_blank">Boston Globe</a>, it's looking more an more likely that Gabe Pruitt will not be a Boston Celtic next season. </p> <p>   </p> <p>   </p> <p> <b>Fantasy Impact </b><b>: </b>Boston is believed to be trying to move Pruitt before Friday which is when his option deadline is.  Should they not be able to work out a trade, it is believed that they will not pick up his option. </p> jsavino 2009-07-29 10:16:04 <p> <b>Update:</b> The <a href="http://www.startribune.com/sports/wolves/51933312.html" target="_blank">Minneapolis Star-Tribune</a> is reporting that the Milwaukee Bucks have signed first-round pick Brandon Jennings to a two-year contract worth $3.75 million. Jennings was the 10th overall pick in this year's draft. </p> <p> <br /> <b>Fantasy Impact: </b>Jennings has a boat load of talent and confidence, but it remains to be seen if he can play in the NBA.  He struggled over in Europe, but does have potential to make an impact with the Bucks.  He isn't a fantasy option this year, but could be in the future. </p> jsavino 2009-07-29 10:12:35 <p> <b>Update:</b> The Indiana Pacers have officially signed free-agent point guard Earl Watson to a multi-year contract, according to <a href="http://www.tsn.ca/nba/story/?id=286031" target="_blank">TSN</a>. Financial terms of the deal were not disclosed by the team. <br /> <b></b> </p> <p> <b>Fantasy Impact: </b>This reported deal has been in the works for a few weeks, and now it is official.  The team has limited options as the guard position now that Jarret Jack is gone, so Watson should seek some playing time in Indy. </p> <b></b> jsavino 2009-07-28 13:02:55 <p> <b>Update: </b>Matt Harpring, dealing with chronic knee problems, may retire, according to the Salt Lake Tribune. </p> <p> <b>Fantasy Impact: </b>Harping is done being a fantasy option. He averaged just 11 minutes and four points per game last season, and should not be drafted in fantasy leagues even if he does remain in the NBA. </p> jsavino 2009-07-28 12:49:12 <p> <b>Update:</b> The Mavs are expected to offer veteran free agent forward Tim Thomas a deal on Tuesday, according to ESPN. Terms of the deal have not been disclosed, but Thomas' agent expects him to sign with the team. </p> <p> <b>Fantasy Impact: </b> Thomas landing in Dallas would be his 8th career team. His last two roles were both reserve roles for the Knicks and Bulls, where he managed to put up just 8.5 ppg and 3.1 rpg. </p> jsavino 2009-07-28 12:43:26 <p> <b>Update: </b>The Miami Heat are in "hot pursuit" of free agent guard Jamaal Tinsley, according to Yahoo! Sports. </p> <p> <b>Fantasy Impact:</b> Tinsley has played just 39 games the past two years, and is a huge risk fantasy wise, but landing in Miami makes it easier to pull the trigger on him. </p> jsavino 2009-07-27 10:59:23 <p> <b>Update:</b> The Knicks and restricted FA Nate Robinson are close to agreeing on a one-year contract worth $5 million to $6 million, according to Jonathan Abrams of the NY Times. Robinson recently declined a two-year, $10 million offer from the Greek team Olympiakos. Signing with the Knicks would allow him to enter the market as an unrestricted FA next summer, when more teams will have the cap space to offer long-term contracts. Signing Robinson would "almost certainly end the Knicks' interest in signing point guard Ramon Sessions from Milwaukee. </p> <p> <b>Fantasy Impact:</b> Robinson is a solid player and should be able to have some decent fantasy value playing in New York as he is sure to get a ton of playing time. </p> )

    1

 

Can anybody tell by this why nothing is being inserted?

 

Thanks

[Catfish]

Not sure. If the query is successful and there is data in the array, but nothing is being inserted, it sounds like there is a problem, but not with your script. Ensure you are refreshing your database view when you check if the data is there, or try doing a SELECT * FROM tableName to see whats in the database table.

 

Just an extra question:

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

 

is this block of code for future development? or does it get used? surely that would make a syntax error if the condition is met?

[timmah1]

Catfish,

I scraped that and did it a little bit different

This is what I'm working on now

The problem I'm having here is I get 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', '

    Update: The Knicks have the exclusiv' at line 1

 

Here is the code now

<?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", "xxxx", "xxxx");
mysql_select_db("xxxx", $db);

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

$sql = "INSERT INTO feeds(title, content) VALUES";
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 .= "('{$item_title}', '{$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<br />
<blockquote>". mysql_error($db) ."</blockquote>";
}

?> 

[Catfish]

It's just a mysql syntax error. You've probably fixed already, but it's most likely encountering a character in the content string that is illegal in the MYSQL statement without being escaped. Trying using mysql_escape_string() on the variable that holds the content when you add it to the MYSQL query string.