Jump to content


Photo

xml.aspx to a SQL input


  • Please log in to reply
5 replies to this topic

#1 jacko_162

jacko_162

    Advanced Member

  • Members
  • PipPipPip
  • 380 posts
  • LocationStaffordshire, United Kingdom

Posted 12 February 2013 - 04:57 PM

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, 12 February 2013 - 04:59 PM.

i love the phpfreaks :)

#2 jacko_162

jacko_162

    Advanced Member

  • Members
  • PipPipPip
  • 380 posts
  • LocationStaffordshire, United Kingdom

Posted 12 February 2013 - 04:59 PM

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
i love the phpfreaks :)

#3 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 12 February 2013 - 06:15 PM

SimpleXML is the answer you're looking for. ;)
Keeping it simple.

#4 jacko_162

jacko_162

    Advanced Member

  • Members
  • PipPipPip
  • 380 posts
  • LocationStaffordshire, United Kingdom

Posted 12 February 2013 - 07:40 PM

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
i love the phpfreaks :)

#5 jacko_162

jacko_162

    Advanced Member

  • Members
  • PipPipPip
  • 380 posts
  • LocationStaffordshire, United Kingdom

Posted 12 February 2013 - 07:58 PM

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, 12 February 2013 - 07:59 PM.

i love the phpfreaks :)

#6 jacko_162

jacko_162

    Advanced Member

  • Members
  • PipPipPip
  • 380 posts
  • LocationStaffordshire, United Kingdom

Posted 12 February 2013 - 09:08 PM

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..
i love the phpfreaks :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com