xml.aspx to a SQL input

[jacko_162]

I have access to the following url;

 

https://api.eveonlin...****&extended=1 (hidden some data!)

 

which relays the following;

 

<eveapi version="2">
<currentTime>2013-02-12 21:50:36</currentTime>
<result>
<rowset name="members" key="characterID" columns="characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles">
<row characterID="*****" name="*****" startDateTime="2012-03-16 23:41:00" baseID="0" base="" title="" logonDateTime="2013-02-12 15:32:52" logoffDateTime="2013-02-12 15:37:53" locationID="60014911" location="1V-LI2 III - Moon 2 - MbI BepHyJIucb" shipTypeID="-1" shipType="" roles="0" grantableRoles="0"/>
</rowset>
</result>
<cachedUntil>2013-02-13 00:40:59</cachedUntil>
</eveapi>

 

how would i parse this information and add the results to a mysql table?

 

i will use this file as a CRON job to run every 6 hours to read the XML file, pull the contents and add them a database (mysql)

 

i will then code further php pages to view the information, which i should be able to do.

 

im completely lost on how to achieve this and struggling to find code snippets to work with.

 

any help appreciated.

[jacko_162]

forgot to add;

 

i will add the following data to a sql table;

 

 

characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles

[Christian F.]

SimpleXML is the answer you're looking for.

[jacko_162]

ok working with what i found for SimpleXML i have developed the following code;

 

<?php


// INCLUDE DB CONNECTION FILE
include("connect.php");

// CHANGE THE VALUES HERE
$keyID    = 'KeyID';
$vCode    = 'vCode';


// URL FOR XML DATA
$url = 'https://api.eveonline.com/corp/MemberTracking.xml.aspx?keyID='$keyID'&vCode='vCode'&extended=1';


// RUN XML DATA READY FOR INSERT 
$xml = simplexml_load_file($url);
$data = $fields = array();
foreach ($xml->xpath('row') as $row) {
        $fields = array_keys((array)($row));
        $data[] = '(' . join(', ', (array)$row) . ')';
}


// NOW LETS INSERT INTO DATABASE!!
$sql = "INSERT INTO membertracking (" . join(', ', $fields) . ") VALUES\n" ;
$sql .= join (",\n", $data);


// CHECK OUTPUTS AND VERIFY
//echo "<pre>$sql</pre>";
?>

 

 

my questions are;

 

1) how do i split the feed to insert separate information for each piece of data for example;

 

row 1 should have data for each of the following (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles)

 

2) how do i need to setup my Database table?

 

thank you thus far

[jacko_162]

now im getting the following error:

 

Failed to open XML feed https://api.eveonlin..................

 

from my new code:

 

<?php

// INCLUDE DB CONNECTION FILE
include("connect.php");

// CHANGE THE VALUES HERE
$keyID = '*****';
$vCode = '*****';

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

// RUN XML DATA READY FOR INSERT
if (file_exists($url)) {
$xml = simplexml_load_file($url);
$data = $fields = array();
foreach ($xml->xpath('row') as $row) {
$fields = array_keys((array)($row));
$data[] = '(' . join(', ', (array)$row) . ')'; }

// NOW LETS INSERT INTO DATABASE!!
$sql = "INSERT INTO membertracking (" . join(', ', $fields) . ") VALUES\n" ;
$sql .= join (",\n", $data);

} else {
// IF FAILURE SHOW ERROR
exit('Failed to open XML feed '.$url);
}
?>

[jacko_162]

i need some sleep

 

<?php


// INCLUDE DB CONNECTION FILE
include("connect.php");

// CHANGE THE VALUES HERE
$keyID    = '*****';
$vCode    = '*****';


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


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


// Loop Through Names
foreach ($xml->result->rowset[0] as $value) {
echo "characterID:".$value['characterID']."    name: ".$value['name']."    location: ".$value['location']."<br />";
};

// NOW LETS INSERT INTO DATABASE!!
THIS IS WHAT I NOW NEED TO FIGURE OUT!!!
?>

 

now i just need to loop through the results and add them to the database and include all the other info. the echo loop is just for checking..