xml to array to mysql trouble

[molsonbubba]

I have been working on this for days now, I am stuck, need your guys help please. I am trying to fetch XML from URL, get into to an array and then into mysql db. XML has 797 lines.  Number of lines may be less one day and greater the next. The code I have so far inserts same line 797 times lol instead of 797 unique lines once . XML data is all integer. I tried foreach ($array as $key =>$val) as well, I cannot get it to work. XML and code is below. (XML is cut down from 797 lines for purposes of this post).

 

locationID, itemID, typeID, quantity only are needed.

 

<eveapi version="2">

   <currentTime>2013-11-30 20:03:22</currentTime>

        <result>

             <rowset name="assets" key="itemID" columns="itemID,locationID,typeID,quantity,flag,singleton">

                   <row itemID="255941808" locationID="66011858" typeID="27" quantity="1" flag="70" singleton="1" rawQuantity="-1">

                        <rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">

                            <row itemID="1000634952561" typeID="24699" quantity="1" flag="116" singleton="1" rawQuantity="-1"/>

                            <row itemID="1005070355787" typeID="940" quantity="1" flag="116" singleton="1" rawQuantity="-1"/>

                            <row itemID="1005259834973" typeID="40" quantity="3646" flag="117" singleton="0"/>

                         </rowset>

                      </row>

                    <row itemID="730126633" locationID="66014546" typeID="27" quantity="1" flag="4" singleton="1" rawQuantity="-1">

                        <rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">

                              <row itemID="1006719601082" typeID="10246" quantity="1" flag="4" singleton="1" rawQuantity="-1"/>

                              <row itemID="1008808286035" typeID="10246" quantity="1" flag="4" singleton="1" rawQuantity="-1"/>

                              <row itemID="1012628971303" typeID="18610" quantity="4" flag="4" singleton="0"/>

                         </rowset>

                      </row>

                </rowset>

            </result>

     <cachedUntil>2013-11-30 21:29:29</cachedUntil>

</eveapi>

<?php

// Request API from server and create array from returned XML

$keyID="somekey";
$vcode="somevcode";
$characterID="someid";
$url = "https://api.eveonline.com/corp/AssetList.xml.aspx?keyID=".$keyID."&vcode=".$vcode."&characterID=".$characterID."";

$data = file_get_contents($url);
$list = new SimpleXMLElement($data);

foreach ($list->result->rowset->row as $row)
foreach ($row->rowset->row as $row1)
{
$assets[] = array ('locationID' => $row['locationID'],
		   'itemID'   	=> $row1['itemID'], 
		   'typeID' 	=> $row1['typeID'],
		   'quantity'   => $row1['quantity'],
);
}

//Connection settings
require_once ($_SERVER["DOCUMENT_ROOT"]."/includes/dbconnection_local_corpassets.php");

//Connection settings from above line, not in live code
$host="localhost"; // Host name
$username="someuser"; // Mysql username
$password="somepass"; // Mysql password
$db_name="somed"; // Database name

try {
    $pdo = new PDO('mysql:host=localhost;dbname=evecorp', $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec ('SET CHARACTER SET "utf8"');
}
catch(PDOException $e)
{
    echo "ERROR!: " . $e->getMessage() . "<br/>";
    die();
}
echo date('l, F jS Y.') .  "<br/>";
echo "Connection to " . $myserver . " established." .  "<br/>";
echo "<br/>";


//Try to insert, count # of records in array

$pdo->beginTransaction(); 
try
{
$countArray = count($assets);
echo "We have $countArray records to enter into the database";

$stmt = $pdo->prepare('INSERT INTO `assets` (`locationID`, `itemID`, `typeID`, `quantity`) VALUES(:locationID, :itemID, :typeID, :quantity);');

  foreach ($assets as $row1)
  $affectedRows = $stmt->execute(array(':locationID'=>$row['locationID'], ':itemID'=>$row1['itemID'], ':typeID'=>$row1['typeID'], ':quantity'=>$row1['quantity']));
  print_r($pdo->errorInfo());
  $pdo->commit();

} catch (PDOException $e) {
    echo "ERROR!: " . $e->getMessage() . "<br/>";
	die();
}
echo "<br/>" . "Database $db_name updated with $affectedRows row(s).";
?>

Edited November 30, 2013 by molsonbubba

[denno020]

Are you getting any errors when you run this code? You haven't initialised $assets as an array, so php won't like you trying to append to the end of it with $assets[] = ...

[Barand]

 

$arr[key] = value;
$arr[] = value;
// key may be an integer or string
// value may be any value of any type If $arr doesn't exist yet, it will be created, so this is also an alternative way to create an array.

[molsonbubba]

nope no errors.

how do i initilize this array, something like this would be correct?

$assets = array();

$assets =  ('locationID' => $row['locationID'],
                   'itemID'     => $row1['itemID'],

                   'typeID'     => $row1['typeID'],
                   'quantity' => $row1['quantity'],

 

cut down version of $var_dump($assets);

array(4) { ["locationID"]=> object(SimpleXMLElement)#8 (1) { [0]=> string( "66014546" } ["itemID"]=> object(SimpleXMLElement)#9 (1) { [0]=> string(13) "1005281245364" } ["typeID"]=> object(SimpleXMLElement)#10 (1) { [0]=> string(5) "10246" } ["quantity"]=> object(SimpleXMLElement)#11 (1) { [0]=> string(1) "1" } } array(4) { ["locationID"]=> object(SimpleXMLElement)#6 (1) { [0]=> string( "66014546" } ["itemID"]=> object(SimpleXMLElement)#13 (1) { [0]=> string(13) "1008808366433" } ["typeID"]=> object(SimpleXMLElement)#14 (1) { [0]=> string(5) "10246" } ["quantity"]=> object(SimpleXMLElement)#15 (1) { [0]=> string(1) "1" } } array(4) { ["locationID"]=> object(SimpleXMLElement)#12 (1) { [0]=> string( "66014546" } ["itemID"]=> object(SimpleXMLElement)#10 (1) { [0]=> string(13) "1008944485266" } ["typeID"]=> object(SimpleXMLElement)#9 (1) { [0]=> string(5) "18610" } ["quantity"]=> object(SimpleXMLElement)#8 (1) { [0]=> string(1) "1" } } array(4) { ["locationID"]=> object(SimpleXMLElement)#11 (1) { [0]=> string(

Edited December 1, 2013 by molsonbubba

[denno020]

 

nope no errors.

how do i initilize this array, something like this would be correct?

$assets = array();

$assets =  ('locationID' => $row['locationID'],

                   'itemID'     => $row1['itemID'],

                   'typeID'     => $row1['typeID'],

                   'quantity' => $row1['quantity'],

 

This is how I thought it had to be done:

 

 

$assets = array();
$assets[] = array(
                   'locationID' => $row['locationID'],
                   'itemID'       => $row1['itemID'],
                   'typeID'        => $row1['typeID'],
                   'quantity'     => $row1['quantity']);

 

But apparently it doesn't have to be done that way..

[molsonbubba]

I am doing something wrong with the the array it seems. $var_dump shows every key in the array as [0] ?

[molsonbubba]

nope, never mind, keys are correct. just checked again. sorry. keays are repeating though as in not unique.

any pointers how to make the code work correctly?

[molsonbubba]

OK, really not sure I understand this now. Changing db columns to Null = Yes fixed the issue. Please help to understand why. Thanks guys!