Accessing the CCP DB programmatically/PHP-PDO

From EVEDev
Jump to: navigation, search

Accessing the CCP DB programmatically using PHP, PDO and connecting in this case to MySQL.

Please see the PDO documentation for other server types, PDO supports many.

Make sure to notice the unicode commands sent to the server once connected.

Code Example

example.php

<html><head></head><body>
<?php
 
try {
  $nl = "\n";
 
  $db['type'] = 'mysql'; // change to your server type per pdo docs
  $db['host'] = 'localhost'; // change to your database server
  $db['user'] = 'username'; // change to your database username
  $db['pass'] = 'password'; // change to your database user password
  $db['schema'] = 'mydatabase'; // change to your database name 
 
  $dbh = new PDO($db['type'].':host='.$db['host'].';dbname='.$db['schema'], $db['user'], $db['pass']);
 
  // the SSD is in unicode, so to avoid seeing weird characters when displaying text fields to the user, make sure our connection is transfering unicode
  // no dash in utf8, this may be mysql specific
  $dbh->exec('SET NAMES utf8');
  $dbh->exec('SET CHARACTER SET utf8');
 
  // a sample query
  $sql = 'SELECT 
    `t1`.`factionName` AS `name`
  FROM `chrFactions` AS `t1`
  ORDER BY `t1`.`factionName` ASC';
  $sth = $dbh->prepare($sql);     
  if (!$sth->execute())
    throw new PDOException('execute');
  $ret = $sth->fetchAll(PDO::FETCH_ASSOC);
 
  if (empty($ret)) // if no results is an error
    throw new PDOException('no results');
 
  // do something with the data here
  var_dump($ret);
 
} catch (PDOException $e) {
  echo '<h2>Database error</h2>'.$nl;
  echo '<p>Message: '.$e->getMessage(). '</p>'.$nl;
  if ($dbh)
    echo '<p>Info: '.print_r($dbh->errorInfo(), true).'</p>'.$nl;
  die(); // quit program
}
 
?>
</body></html>


the result should look something like this:

array(20) { [0]=> array(1) { ["name"]=> string(12) "Amarr Empire" } [1]=> array(1) { ["name"]=> string(15) "Ammatar Mandate" } [2]=> array(1) { ["name"]=> string(12) "Angel Cartel" } [3]=> array(1) { ["name"]=> string(21) "Blood Raider Covenant" } [4]=> array(1) { ["name"]=> string(13) "Caldari State" } [5]=> array(1) { ["name"]=> string(16) "CONCORD Assembly" } [6]=> array(1) { ["name"]=> string(19) "Gallente Federation" } [7]=> array(1) { ["name"]=> string(16) "Guristas Pirates" } [8]=> array(1) { ["name"]=> string(11) "Jove Empire" } [9]=> array(1) { ["name"]=> string(14) "Khanid Kingdom" } [10]=> array(1) { ["name"]=> string(17) "Minmatar Republic" } [11]=> array(1) { ["name"]=> string(22) "Mordu's Legion Command" } [12]=> array(1) { ["name"]=> string(3) "ORE" } [13]=> array(1) { ["name"]=> string(15) "Sansha's Nation" } [14]=> array(1) { ["name"]=> string(9) "Serpentis" } [15]=> array(1) { ["name"]=> string(22) "Servant Sisters of EVE" } [16]=> array(1) { ["name"]=> string(12) "The InterBus" } [17]=> array(1) { ["name"]=> string(32) "The Society of Conscious Thought" } [18]=> array(1) { ["name"]=> string(13) "The Syndicate" } [19]=> array(1) { ["name"]=> string(13) "Thukker Tribe" } }

Personal tools