Jump to content

xml.aspx to a SQL input


jacko_162

Recommended Posts

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.

Edited by jacko_162
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Edited by jacko_162
Link to comment
Share on other sites

i need some sleep :tease-01:

 

<?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..

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.