Collecting .XML data to input into database

[jacko_162]

At the moment I collect data from an .XML file and it gets inserted into a database and it works great however the way in which the .xml files work has changed and I need to update my script to accommodate,

 

before everything was processed by 1x .xml file and i collected data, now its done via 2x .xml files.

 

So here is my current script;

<?php 
// INCLUDE DB CONNECTION FILE
include("includes/connect.php");
 
// CHANGE THE VALUES HERE
include("includes/config.php");

// URL FOR XML DATA
$url = "https://api.eveonline.com/corp/StarbaseList.xml.aspx?keyID=".$keyID."&vCode=".$vCode."";

// RUN XML DATA READY FOR INSERT
$xml = simplexml_load_file($url);

// Loop Through Names
$insertValues = array();
$modifiedTS = date('Y-m-d h:i:s');
foreach ($xml->result->rowset[0] as $value)
{
        //Prepare the values
        $itemID = $value['itemID'];
        $typeID = mysql_real_escape_string($value['typeID']);
        $locationID = $value['locationID'];
        $moonID = $value['moonID'];
        $state = mysql_real_escape_string($value['state']);
        $stateTimestamp = mysql_real_escape_string($value['stateTimestamp']);
        $onlineTimestamp  = $value['onlineTimestamp'];
        $standingOwnerID = $value['standingOwnerID'];

//Create and run ONE INSERT statement (with UPDATE clause)
    $insert = "INSERT INTO `ecmt_poslist` (itemID,typeID,locationID,moonID,state,stateTimestamp,onlineTimestamp,standingOwnerID,last_modified) VALUES('$itemID','$typeID','$locationID','$moonID','$state','$stateTimestamp','$onlineTimestamp','$standingOwnerID','$modifiedTS') ON DUPLICATE KEY UPDATE 
state='$state',
stateTimestamp='$stateTimestamp',
onlineTimestamp='$onlineTimestamp',
last_modified = '$modifiedTS'";
						
mysql_query($insert) or die(mysql_error());


//ERROR CHECKING OPTION ONLY!
//echo $insert;
//echo "<br><br>";

};
//UPDATE last time this script ran and insert timestamp into Database 
$timeNow = date('Y-m-d H:i:s', strtotime('-1 hour'));
$insertTime = "UPDATE `ecmt_API` SET time=1, time='$timeNow'";
mysql_query($insertTime) or die(mysql_error());

//Run query to delete records that were not just inserted/updated
$delete = "DELETE FROM `ecmt_poslist` WHERE last_modified < '$modifiedTS'";
mysql_query($delete) or die(mysql_error());

?>

and here is the wiki page about the .xml file and its data;

 

http://wiki.eve-id.net/APIv2_Corp_StarbaseList_XML

 

i now need it to collect more data from a 2nd .xml file but the url will include the $itemID of the items pulled by the above script;

 

http://wiki.eve-id.net/APIv2_Corp_StarbaseDetail_XML

 

now i guess i need to put the code in the above foreach loop so I can say for each $itemID gather data from the second .xml file and input into a seperate table in my database named: `ecmt_poslistdetails`

 

but i have no idea where to start as the rowset and layout of the 2nd .xml file is different, can anyone help me.. i'm not even sure if I wrote out this post to make sense from someone looking in from outside or not. its hard to explain.

 

but hopefully the links provided will help.

 

Ultimately I hope to have 2 tables in my database

ecmt_poslist and ecmt_poslistdetails both containing the data from the above 2x .xml files.

[jacko_162]

forgot to add;

 

on the 2nd .xml file I only need the data from the "fuel" rowset so typeID and quantity.

 

so second table in database would have 3 columns "itemID (from first .xml!), typeID (from 2nd .xml) and quantity (from 2nd .xml)"