Difference between revisions of "API/Db"

From aMember Pro Documentation
Jump to: navigation, search
(Created page with "aMember Pro utilizes http://en.dklab.ru/lib/DbSimple/ DbSimple library for easy database access. It provides most intuitive and error-prone interface compared to another libr...")
 
Line 1: Line 1:
aMember Pro utilizes [[http://en.dklab.ru/lib/DbSimple/ DbSimple]] library for easy database access. It provides most intuitive and error-prone interface compared to another libraries. We are using slightly modified version of the library, with addition of 3 functions:
+
aMember Pro utilizes [[http://en.dklab.ru/lib/DbSimple/ DbSimple]] library for easy database access. It provides most intuitive and error-prone interface. 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
 
* queryResultOnly($sql, $params=...) : return mysql query identifier (PDOStatement in our case) instead of fetched rows. Useful for long results
Line 10: Line 10:
 
== How to access database object to run queries ==
 
== How to access database object to run queries ==
 
From any part of the program, you can run:
 
From any part of the program, you can run:
<source>
+
<source lang="php">
 
// get full user records
 
// get full user records
$users = amDb()->select("SELECT * FROM ?_user WHERE state=? LIMIT ?d", 'NY', 10);
+
$users = Am_Di::getInstance()->db->select("SELECT * FROM ?_user WHERE state=? LIMIT ?d", 'NY', 10);
 
// or get only assotiative array user_id => login
 
// or get only assotiative array user_id => login
$idLogin = amDb()->selectCol("SELECT user_id as ARRAY_KEY, login FROM ?_user WHERE country = 'US'");
+
$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
 
// there is also an unique ability to skip part of query on some condition
 
// for example:
 
// for example:
 
$country = 'UK'; $state = null;
 
$country = 'UK'; $state = null;
$id = amDb()->selectCell("SELECT user_id FROM ?_user WHERE country=? { AND state=? } LIMIT 1", $country, $state ? state : DBSIMPLE_SKIP);
+
$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  
 
// 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
 
// and if you pass value different from DBSIMPLE_SKIP constant this part of query will be inserted, say
 
$country = 'US'; $state = 'CA';
 
$country = 'US'; $state = 'CA';
$id = amDb()->selectCell("SELECT user_id FROM ?_user WHERE country=? { AND state=? } LIMIT 1", $country, $state ? state : DBSIMPLE_SKIP);
+
$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  
 
// will run as "SELECT user_id FROM am_user WHERE country='US' AND state='CA' LIMIT 1  
  
Line 29: Line 29:
 
// ?a automatically converted to array, for example
 
// ?a automatically converted to array, for example
 
$arr = array('login' => "x'x", 'email' => 'x@x.x');
 
$arr = array('login' => "x'x", 'email' => 'x@x.x');
amDb()->query("UPDATE ?_user SET ?a", $arr);
+
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'"
 
// will be automatically executed as "UPDATE am_user SET `login`='x\'x', `email`='x@x.x'"
 
</source>
 
</source>

Revision as of 07:05, 17 November 2011

aMember Pro utilizes [DbSimple] library for easy database access. It provides most intuitive and error-prone interface. 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'"