EVE API Tutorial Part IV – Connect API data with the Static Data Dump

During the first part of this tutorial – the preparations – we downloaded and installed the Static Data Dump into the evesdd_crucible_11 database. Now we will make use of this.

In this chapter we will query the AssetList API function to get the list of assets a character has and use the Static Data Dump to find out, what all those IDs stand for.

Let’s begin by downloading and unpacking the api-tutorial-part4-start.zip archive into the eve-charinfo folder. This is mainly the result of the last chapter, with one addition:
In the display.php file, there is an additional block of HTML, at the end of the file. This is a table we want to populate with the assets.

If you didn’t complete Part I and II, create a database schema and import the Static Data Dump as discribed there.

Fetching the asset list

Let’s begin by doing an additional API request. If you understood the CharacterSheet request of the last part, there will be no surprise here. Add this API call, right after the first one:

// Request the asset list
$assets = $request->AssetList($arguments);
$assets = $assets->assets->toArray();

The AssetList requires the same arguments or parameters as the CharacterSheet, so we can simply re-use them here. The part where we turn the result into an array is a little different though. That’s because that two functions return a XML with a different layout. Take a look at the reference pages for all the details.

Now let’s get down to the HTML table and use the assets array to generate one table row per asset. For this, wrap a foreach statement between the <tr> tags inside the tbody section at the end of the file and place the contents inside the cells. For items that contain other items (like fitted ships or station containers), we only want to show how many items are in it. That keeps things a little cleaner for now.

<tbody>
<?php
 
foreach ($assets as $asset):
	$qty = $asset['quantity'];
	$qtyContents = 0;
 
	if (isset($asset['contents'])) {
		$qtyContents = count($asset['contents']);
	}
 
	$location = $asset['locationID'];
	$item = $asset['typeID'];
?>
	<tr>
		<td><?php echo $location; ?></td>
		<td><?php echo $item; ?></td>
		<td><?php echo $qty; ?></td>
		<td><?php echo $qtyContents; ?></td>
	</tr>
 
<?php
 
endforeach;
?>
</tbody>

Now we have a nice list of all the items a character has. Unfortunatly, they are only visible as numbers. Since we are no computers our selfs, we want the names behing those numbers. That’s were the data dump comes in.

Get names for items from the data dump

Before we can start using the dump, we need to connect to it. It is located inside its own database, so we need a new connection. To archive this, we create a new function in the functions.php file.

function getDumpDb() {
	static $db;
	if (!$db) {
		$db = new PDO('mysql:host=localhost;dbname=evesdd_crucible_11', 'root', '');
	}
	return $db;
}

Now where we have the connection we need to determine what we want. Looking at the currently generated table, its pretty obvious.

We want the locationIDs to be replaced with locationNames
We want the typeIDs (in EVE, items are always referenced as types, the term “item” actually refers to an instance of a type in an “inventory”, like in space, in a hangar or somewhere else) to be gone as well and the typeNames instead.

So we need to prepare two queries. One for the type (the item in question) and the location (where the item lives). Add this code right before the foreach statement:

$selectType = getDumpDb()->prepare('
	SELECT
		`typeName` AS `name`
	FROM
		`invTypes`
	WHERE
		`typeID` = ?');
 
$selectLocation = getDumpDb()->prepare('
	SELECT
		`itemName` AS `name`
	FROM
		`mapDenormalize`
	WHERE
		`itemID` = ?');

And replace the code for setting the $location and $item variables with this:

// Get the location
$selectLocation->execute(array($asset['locationID']));
$result = $selectLocation->fetchAll(PDO::FETCH_ASSOC);
$location = $result[0]['name'];
 
// Get the item type
$selectType->execute(array($asset['typeID']));
$result = $selectType->fetchAll(PDO::FETCH_ASSOC);
$item = $result[0]['name'];

Now reload the page and you will see the names in here.

And that’s it. Not so difficult right? With the knowledge you gained now, go into the wild and write your own cool applications or enhance this one, eg to have a recruitment – char – checker tool or to show off with your char or whatever you want.

The final program can be downloaded at api-tutorial-part4-end.zip.

Happy coding!

15 Responses to EVE API Tutorial Part IV – Connect API data with the Static Data Dump

ceri says:

5. December 2012 at 14:06

I don’t know where i’ve gone wrong here. All i get in the browser window is the message ‘Select a character’ with the character selector field below it with an empty dropdown box. There’s no facility for entering the character information or anything else that’s present in the ‘index.php’ file. As a last resort i copied over the entire content of the part 4 zip file and refreshed my browser but still that same result. It’s a great tutorial and i’m sure there’s something stupid i’ve done/not done… Any suggestions please?

- Trenker says:
  
  18. December 2012 at 21:32
  Looks like a PHP error. Try adding this to the top of the index.php and see if there was an error that got silenced:
  <?php error_reporting(-1);
  If this does not work, please send me a mail or ask at the forums at the EVE Technology Lab.
Erik says:

12. March 2012 at 10:18
$selectLocation = getDumpDb()->prepare(' SELECT `itemName` AS `name` FROM `mapDenormalize` WHERE `itemID` = ?');
This should be:
$selectLocation = getDumpDb()->prepare(' SELECT `itemName` AS `name` FROM `invnames` WHERE `itemID` = ?');
The mapDemormalization table does not function properly with assets stored in stations, and will only function properly if assests are floating in space (ie. your sitting in your ship outside a station).
- Trenker says:
  
  13. March 2012 at 14:02
  
  I cannot confirm this. The mapDenormalize table works fine as long as it is a “top level” asset – not inside a container. Using the invNames table works fine of course, but AFAIK mapDenormalize too.
  
greg says:

1. March 2012 at 13:07

Really nice tuto. Thanks for your work.
Little question. I have tried to find the solution on the pheal site to no avail.
On my linux box no problem, on my windows I get :

Error 0: API Date could not be read / parsed, original exception: SSL certificate problem, verify that the CA cert is OK. Details: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

I think that it is a problem to do with the cache directory rather than a real certificate problem.

- Trenker says:
  
  1. March 2012 at 22:37
  No, its a problem with the cert. The API Date thingy is added by Pheal, but has no meaning in this case.
  If nothing works, you can try to use raw http by setting – inside the loadPheal function:
  PhealConfig::getInstance()->api_base = 'http://api.eveonline.com/';
  That does not encrypt the transfered data, but also removes any possible SSL headaches.
Dunhkan says:

26. February 2012 at 14:35

Hi,

First.. thank’s a lot for you work, it’s help me a lot whit the basic of api et library like pheal.
But, i have one probleme, same as Curtis in fact.
I create the folder .pheal-cache, but windows don’t let me put the dot before “pheal-cache”, like “.pheal-cache”.. so i have the same error like Curtis when a i press the “Display selected character” button.

I don’t know how resolve this..

I use Wamp, but i don’t think it’s the problem here..
Can you help me for this?

- Trenker says:
  
  27. February 2012 at 18:23
  
  Windows Explorer does not let you create folders and files that start with a dot, true. Windows – by itself – has no problem with this though, so you can create it with another explorer program, or as described in Follow up to the API tutorial
  
Andy says:

25. February 2012 at 22:13

hi
I did run your code.
Api Example 4 displays lots of items and then breaks error
Notice: Undefined offset: 0 in C:\wamp\www\api5\display.php on line 171
Is that an error when a special item ist missing in the dump db?
Regards Sept

- Trenker says:
  
  27. February 2012 at 18:20
  Yep, the query result seems to be empty in your case, which is rather strange. A quick fix could look like this (at line 171):
  $item = empty($result[0]['name']) ? "Unknown item (ID was $item)" : $result[0]['name'];
Curtis Mohr says:

6. February 2012 at 13:37

I love this idea, and thank you for doing this tutorial. I have been trying to figure out the eve api thing for a while and since im still pretty newbish on php anyway its been a struggle. Anywho, I went through all this and I got a problem and noticed something. on step 3, I get this message…

Error 0: Cache directory ‘C:\xampp\htdocs\eve-charinfo/.pheal-cache/myapiid/myapivcode/account/APIKeyInfo/’ isn’t writeable

when I get to step 4 it gives me a cant find loadPheal() error message. I reloaded the pages with the completed files in the zip file and get this message…I put that function back into functions.php and get the above Error 0 message again…Help please!

- Trenker says:
  
  9. February 2012 at 16:12
  
  For the cache directory problem: Pheal does not create the “.pheal-cache” folder, you have to create it yourself. I added this info to the tutorial page.
  
  - sam says:
    
    17. December 2012 at 14:39
    
    I have created the pheal-cache folder (as useable in windows directory structure) and have made the necessary changes to the php. All good so far, but when i ran the query by pressing the ‘display selected character’ button, I get the same error as curtis did: “Error 0: Cache directory ‘C:\xampp\htdocs\eve-charinfo.localhost/.pheal-cache/*char-id*/*API-Key*/account/APIKeyInfo/’ isn’t writeable”.
    
    I am logged into my computer as Admin and i keep changing the write permissions on the folder (de-selecting ‘read-only’ on folder properties dialog) so that it’s writeable, yet when i re-run the query, it resets the folder to ‘read-only’. I’m pretty stumped tbh and don’t know where to go next. Any help you can give with this issue will be hugely appreciated . If it helps, I am running Windows7 Professional 64-bit and xampp version 1.8.1 (with Apache 2.4.3, PHP 5.4.7, MYSQL 5.5.27). The rest of it works great btw, it’s just this last step =/
    
    Great tutorial, looking forward to more
    
    - Trenker says:
      
      18. December 2012 at 21:22
      
      The error message says: “…/.pheal-cache/”, but you said you created a folder with the name “../pheal-cache/”, see the dot?
      
      You have to adjust the path used by pheal in the config, in this case:
      
      PhealConfig::getInstance()->cache = new PhealFileCache(__DIR__ . '/pheal-cache/');
      
Dave says:

31. December 2011 at 01:22

Hey there

Thank you very very much for this superior explanation. I was googling pretty hard to find a tutorial errr well for noobish php users trying to get comfortable with the eve api

*hug* Dave