katropine / kmapper
PHP-MySql data layer
1.1.0
2016-01-11 10:38 UTC
Requires
- php: >=5.3.0
README
KMapper library is a PHP toolkit for accessing and manipulate MySql database. It provides a query builder class called TableMapper. KMapper is a PDO wrapper library. Data is returned as associative array.
Instructions
First create a kmapper.php
file in your /app/config
or /application/config
or /config
directory with content.
- For non MVC framework use define
kmapper.php
config path withdefine('KMAPPER_CONFIG_LOCATION', '/my/cistom/path')
return array( // default mandatory 'default' => array( 'host' => 'localhost', 'dbname' => 'kdbtest', 'user' => 'root', 'password' => 'superpass', 'prefix' => '', 'pdoattributes' => array( array(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC), array(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION), array(\PDO::ATTR_EMULATE_PREPARES, false) ) ), 'db1' => array( 'host' => 'localhost', 'dbname' => 'otherdatabase', 'user' => 'root', 'password' => 'superpass', 'prefix' => 'test', 'pdoattributes' => array( array(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC), array(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION), array(\PDO::ATTR_EMULATE_PREPARES, false) ) ) );
Execute first query:
$DataObject = \KMapper\MySql::query("SELECT * FROM t1"); $DataObject = \KMapper\MySql::execute( "SELECT * FROM `#__t1` WHERE id = ? AND age = ?", array( array(12,\PDO::PARAM_INT), array(25, \PDO::PARAM_INT) ) ); $DataObject = \KMapper\MySql::execute( "SELECT * FROM `#__t1` WHERE id = :id AND age = :age", array( array(':id' => 12,\PDO::PARAM_INT), array(':age' => 25, \PDO::PARAM_INT) ) );
Table Prefix:
#__ is a placeholder, it will be replaced with the value from 'prefix' in config.
Multiple database connections
$options['connection'] = new KMapper\MySqlDbConnect('db1'); $DataObject = \KMapper\MySql::query("SELECT * FROM #__t1", $options); $DataObject = \KMapper\MySql::execute("SELECT * FROM #__t1 WHERE id = ?", array(12), $options);
Query Builder
$UserMP = new \KMapper\TabelMapper("#__user", 'usr'); $UsersDataObject = $UserMP->setSelect(array("usr.first_name", "usr.last_name", "addr.zip")) // table1, joinField1, onTable2, onField2, table1Alias ->setInnerJoin("#__address", "id_user", "usr", "id", "addr") ->setWhere("usr.status != ? AND usr.smart = ? AND (addr.zip = ? OR addr.zip = ?)", array('banned', false, '23000', '21000')) ->setOrderBy("usr.name ASC") ->fetchAll(); var_dump($UsersDataObject->toArray()); var_dump($UsersDataObject->toJson());
Insert & Update
$UserMP = new \KMapper\TabelMapper("#__user"); $data = array( 'first_name' => "Fu", 'last_name' => "Bar" ); // INSERT, no id provided $UserMP->save($data); $data = array( 'id' => 22, 'first_name' => "Fu", 'last_name' => "Bar" ); // UPDATE where id = 22 $UserMP->save($data);
In case "id" is not the primary key name, key has to be defined:
$UserMP = new \KMapper\TabelMapper("#__user"); $UserMP->setPrimaryKeyName('my_unstandard_id')->save($data);
Mutiple UPDATE & INSERT
$UserMP = new \KMapper\TabelMapper("#__user"); $data = array array('first_name' => 'Kriss', 'last_name' => 'Kristiansen'), array('first_name' => 'Johnny', 'last_name' => 'Johnosn') ); $UserMP->batchSave($data);
WHERE id IN ()
$roles = array(1,2,5,8); // set placeholders $inPlaceholders =\KMapper\MySql::getPlaceholders($roles); $i = KMapper\TableMapper::getInstance() ->setFrom('#__user') ->setWhere("`user_id` = ? AND `group_id` IN ( {$inPlaceholders} )", array_merge(array($user->getId()),$roles)) ->countAll();
Transactions
try{ \KMapper\MySql::transactionBegin()); $last = \KMapper\MySql::query($sqlTask)->getLastID(); if(!$last){ throw new \Exception("Could not insert"); } if(!\KMapper\MySql::query($sqlHierarchy)->isSuccess()){ throw new \Exception("Query error"); } \KMapper\MySql::transactionCommit()); } catch (PDOException $E){ \KMapper\MySql::transactionRollback(); } catch (Exception $E){ \KMapper\MySql::transactionRollback(); }
Install KMapper with Composer
"require": {
"katropine/kmapper" : "dev-master"
}