API/Db

From aMember Pro Documentation
Revision as of 15:39, 19 September 2012 by Alex-scott (Talk | contribs)

Jump to: navigation, search

aMember Pro utilizes [DbSimple] library for easy database access. We are using slightly modified version of the library, with addition of 6 functions:

  • queryResultOnly($sql, $params=...) : return mysql query identifier (PDOStatement in our case) instead of fetched rows. Useful for long results
  • queryQuick($sql) : runs query without any substitution and logging. Useful for long or blob inserts
  • fetchRow(PDOStatement $st) - fetches assotiative array from query id returned by queryResultOnly()
  • fetchArray(PDOStatement $st) - fetches numbered array from query id returned by queryResultOnly()
  • freeResult(PDOStatement $st) - releases query id returned by queryResultOnly()
  • getPrefix() - returns configured tables prefix (You usually do not need it, just add ?_ to SQL before the table name to add prefix)

How to access database object to run queries

From any part of the program, you can run:

// get full user records
$users = Am_Di::getInstance()->db->select("SELECT * FROM ?_user WHERE state=? LIMIT ?d", 'NY', 10);
// or get only assotiative array user_id => login
$idLogin = Am_Di::getInstance()->db->selectCol("SELECT user_id as ARRAY_KEY, login FROM ?_user WHERE country = 'US'");
// there is also an unique ability to skip part of query on some condition
// for example:
$country = 'UK'; $state = null;
$id = Am_Di::getInstance()->db->selectCell("SELECT user_id FROM ?_user WHERE country=? { AND state=? } LIMIT 1", $country, $state ? state : DBSIMPLE_SKIP);
// will run as "SELECT user_id FROM am_user WHERE country='UK' LIMIT 1 
// and if you pass value different from DBSIMPLE_SKIP constant this part of query will be inserted, say
$country = 'US'; $state = 'CA';
$id = Am_Di::getInstance()->db->selectCell("SELECT user_id FROM ?_user WHERE country=? { AND state=? } LIMIT 1", $country, $state ? state : DBSIMPLE_SKIP);
// will run as "SELECT user_id FROM am_user WHERE country='US' AND state='CA' LIMIT 1 
 
// ? parameters are automatically escaped 
// ?d automatically converted to integer
// ?a automatically converted to array, for example
$arr = array('login' => "x'x", 'email' => 'x@x.x');
Am_Di::getInstance()->db->query("UPDATE ?_user SET ?a", $arr);
// will be automatically executed as "UPDATE am_user SET `login`='x\'x', `email`='x@x.x'"