These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Results row from API data query (getting wallet transactions)

Author
Zappity
Kurved Trading
#1 - 2013-05-27 22:25:57 UTC  |  Edited by: Zappity
I am using importXML to pull wallet transaction data from a corporation API:

=importxml("https://api.eveonline.com/corp/WalletTransactions.xml.aspx?keyID=[key]&vCode=[vcode]&characterID=[director's characterid]","//row/@transactionid")

No problem. Changing "//row/@transactionid" to "//row/@quantity" or "//row/@transactiontype" in the next columns gives the desired result but I can't help but feel there must be a better way.

transactionid seems to be a row key. If so, can I pull all the fields I want from the row result and populate the adjacent columns with that data in a single query? I hope that separate importxml calls is not the best way to do this.

I have searched but run out of time before I have to go to work. Anyone know what to do?

Zappity's Adventures for a taste of lowsec and nullsec.

Zappity
Kurved Trading
#2 - 2013-06-10 08:14:55 UTC
Just an extra post in case anyone else comes looking for this. This simple PHP script will pull your corporation wallet transactions from the API and add the new entries to a table in your database called 'transactions'. I doubt it's optimal but it seems to do the job.

Note that your API only holds the last 1000 transactions so you will want to run this every [insert suitable timeframe] to capture all data.

/**
* Get Corporation Wallet transactions from the API and add to mysql table 'transactions'
*/

/** Table details *

$create_sql = " CREATE TABLE transactions (
id int(10) NOT NULL AUTO_INCREMENT,
transactionID bigint(15) NOT NULL,
transactionDateTime datetime NOT NULL,
quantity int(10) NOT NULL,
typeName varchar(100) NOT NULL,
typeID int(10) NOT NULL,
price decimal(12,2) NOT NULL,
clientID bigint(15) NOT NULL,
characterID bigint(15) NOT NULL,
characterName varchar(200) NOT NULL,
stationID int(10) NOT NULL,
stationName varchar(200) NOT NULL,
transactionType varchar(10) NOT NULL,
transactionFor varchar(100) NOT NULL,

PRIMARY KEY (id),
UNIQUE id (id),
KEY id_2 (id) ) " ;

$create_stmt = $db -> prepare( $create_sql ) ;
$create_stmt -> execute() ;

*/

$count = 0;

/** Connect to database */
$db = new PDO('mysql:host=localhost;dbname='your eve db', 'your mysql username', 'your mysql password');

/** EVE API details */
$vcode="your corp API vcode"; //Corporation vcode
$keyid="your corp API keyid"; //Corporation key
$characterid="character id"; //A director's character ID. Must have access to the wallet

/** Pull API data into SimpleXMLElement */
$data = file_get_contents("https://api.eveonline.com/corp/WalletTransactions.xml.aspx?keyID=$keyid&vcode=$vcode&characterID=$characterid") ;
$xml = new SimpleXMLElement($data) ;

/** Get the latest transaction already in the 'transactions' database table */
$maxid_stmt = $db->query('SELECT MAX(transactionID) AS transactionID FROM transactions') ;
$maxid = $maxid_stmt->fetch(PDO::FETCH_ASSOC) ;
echo $maxid['transactionID'].' is maxid.' ;

/** Insert the new data to the database */
foreach ($xml -> result -> rowset-> row as $row)
{
if ( $row['transactionID'] > $maxid['transactionID'] )
{
$insert_stmt = $db->prepare("INSERT INTO transactions(
transactionID,
transactionDateTime,
quantity,
typeName,
typeID,
price,
clientID,
characterID,
characterName,
stationID,
stationName,
transactionType,
transactionFor)
VALUES(
:field1,
:field2,
:field3,
:field4,
:field5,
:field6,
:field7,
:field8,
:field9,
:field10,
:field11,
:field12,
:field13
)") ;

$insert_stmt -> execute( array(
':field1' => $row['transactionID'],
':field2' => $row['transactionDateTime'],
':field3' => $row['quantity'],
':field4' => $row['typeName'],
':field5' => $row['typeID'],
':field6' => $row['price'],
':field7' => $row['clientID'],
':field8' => $row['characterID'],
':field9' => $row['characterName'],
':field10' => $row['stationID'],
':field11' => $row['stationName'],
':field12' => $row['transactionType'],
':field13' => $row['transactionFor']
)) ;

$count++ ;

}
}

/** Report how many new records were written */
echo $count.' affected.' ;

Zappity's Adventures for a taste of lowsec and nullsec.

kaspa101
Original Sinners
Northern Coalition.
#3 - 2013-07-03 01:24:14 UTC
What all do I need to do to get this code to work?
Zappity
Kurved Trading
#4 - 2013-07-03 03:24:57 UTC
kaspa101 wrote:
What all do I need to do to get this code to work?


A basic understanding of HTML, PHP and MYSQL. Then either a WAMP (or similar) install or a hosted PHP/MYSQL site.

Zappity's Adventures for a taste of lowsec and nullsec.