README

一个PDO扩展，将帮助你的开发者生活。

功能

• 提供如sqlArray()、sqlrow()、sqlColumn()、sqlValue()等几种方法，帮助你快速检索结果集
• 你可以断开、重新连接、暂停连接。
• 轻松绑定你的值，例如

$ids = $cnx->sqlColumn("SELECT Id FROM table WHERE CreationDate BETWEEN ? AND ?", ['2013-01-01', '2013-06-01']);
$id = $cnx->sqlValue("SELECT Id FROM table WHERE Id = ?", [35]);
$id = $cnx->sqlValue("SELECT Id FROM table WHERE Id = ?", 35); // You don't need an array if you just have 1 value to bind
$row = $cnx->sqlRow("SELECT * FROM table WHERE Name LIKE :Name AND Id >= :Id", ['Name' => 'foo%', 'Id' => 22]);

• 轻松绑定、运行和调试你的语句

$stmt = $cnx->prepare("SELECT Id FROM table WHERE CreationDate BETWEEN ? AND ?");
$ids = $stmt->sqlColumn(['2013-01-01', '2013-06-01']); // Fetches Ids in an indexed array
var_dump($stmt->debug()->preview()); // SELECT Id FROM table WHERE CreationDate BETWEEN '2013-01-01' AND '2013-06-01'
var_dump($stmt->debug()->duration); // 0.004

• 动态创建占位符

$ids = [2, 5, 8, 24, 26];
try {
    $rows = $cnx->sqlArray("SELECT * FROM table WHERE Id IN (".PDOStatementExtended::PlaceHolders($ids).")", $ids);
}
catch (StmtException $e) {
    var_dump($e->getStmt()->queryString); // SELECT * FROM table WHERE Id IN (?, ?, ?, ?, ?)
    var_dump($e->getStmt()->preview()); // SELECT * FROM table WHERE Id IN (2, 5, 8, 24, 26)
}

// Also works with strings with automatic quote wrapping
$dates = ['2013-01-01', '2013-06-01'];
try {
    $rows = $cnx->sqlArray("SELECT * FROM table WHERE CreationDate IN (".PDOStatementExtended::PlaceHolders($dates).")", $dates);
}
catch (StmtException $e) {
    var_dump($e->getStmt()->queryString); // SELECT * FROM table WHERE CreationDate IN (?, ?)
    var_dump($e->getStmt()->preview()); // SELECT * FROM table WHERE CreationDate IN ('2013-01-01', '2013-06-01')
}

• 由于PDO类型会根据PHP类型自动设置，你不再需要处理PDO::PARAM_INT、PDO::PARAM_STRING等。

• 你提交给prepare()和sql()方法的每个查询都会被哈希并存储以供以后重用。例如

$cnx->sqlArray("SELECT * WHERE foo = ?", 'bar'); // "SELECT * WHERE foo = ?" string is prepared and transformed to a PDOStatementExtended object
$cnx->sqlArray("SELECT * WHERE foo = ?", 'baz'); // "SELECT * WHERE foo = ?" now matches an existing PDOStatementExtended object previously stored and doesn't have to be prepared again

• 此行为可以在任何时候启用或禁用（默认启用）

$cnx->storeStmts(false); // or true

• 你还可以导入现有的PDO实例，避免你需要第二个连接

$pdo    =   new \PDO('mysql:host=localhost', 'user', 'password');
$cnx    =   PDOExtended\PDOExtended::NewInstanceFromPdo($pdo);

完整示例用法

define('PDO_DSN', 'mysql:host=localhost;dbname=test');
define('PDO_USERNAME', 'root');
define('PDO_PASSWORD', null);

// Normal call
$cnx    =   new PDOExtended(PDO_DSN, PDO_USERNAME, PDO_PASSWORD);

// Let's create our working table...
$cnx->sql("CREATE TABLE IF NOT EXISTS
                        TVSeries (
                            Id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
                            Name VARCHAR(255),
                            Channel VARCHAR(40),
                            PRIMARY KEY (Id),
                            UNIQUE (Name)
                        ) ENGINE=InnoDb");

// Example of insert with a prepared statement
$insertStmt =   $cnx->Prepare("INSERT IGNORE INTO TVSeries SET Name = ?");
$tvSeries   =   Array('Games Of Thrones', 'The Big Bang Theory', 'Dexter');

// For each Series, we play the insert statement with a different value
foreach ($tvSeries AS $series)
    $insertStmt->sql($series);
    // $series is mapped to the "Name" bound value - since it's a string, it'll be bound as PDO::PARAM_STR
    // If $series was an integer, it'd be bound as PDO::PARAM_INT automatically

// The expected parameter may also be an array, in case you have more values to bind
$insertStmt =   $cnx->Prepare("INSERT IGNORE INTO TVSeries SET Name = :Name");
foreach ($tvSeries AS $series)
    $insertStmt->sql(Array('Name' => $series));

// Now, let's update the previous rows with the Channel info.
$channels    =   Array('HBO', 'CBS', 'ShowTime');

// You're not obliged to prepare your statement before executing them. It's the sql() method's job.
$cnx->sql("UPDATE TVSeries SET Channel = ? WHERE Name = ?", Array($channels[0], $tvSeries[0]));
$cnx->sql("UPDATE TVSeries SET Channel = :Channel WHERE Name LIKE :Name", Array('Channel' => $channels[1], 'Name' => $tvSeries[1]));
$cnx->sql("UPDATE TVSeries SET Channel = :Channel WHERE Id = :Id", Array('Channel' => $channels[2], 'Id' => 3)); // Id will be cast as PDO::PARAM_INT because 2 is a PHP integer

// Now, let's easily retrieve some infos.

// All the table into a multidimensionnal array.
var_dump($cnx->sqlArray("SELECT * FROM TVSeries"));

// Juste the Big bang theory row
var_dump($cnx->sqlRow("SELECT * FROM TVSeries WHERE Id = ?", 2));

// A list of series
var_dump($cnx->sqlColumn("SELECT Name FROM TVSeries WHERE Channel IN (". PDOStatementExtended::PlaceHolders($channels) .")", $channels)); // PlaceHolders function will output "IN (?, ?, ?)";

// What's the channel of Dexter ?
var_dump($cnx->sqlValue("SELECT Channel FROM TVSeries WHERE Name LIKE :Name", Array('Name' => 'Dexter')));

// Another way to request it
var_dump($cnx->sqlValue("SELECT Channel FROM TVSeries WHERE Name LIKE ?", Array('Dexter')));

// Another way to request it
var_dump($cnx->sqlValue("SELECT Channel FROM TVSeries WHERE Name LIKE ?", 'Dexter'));

// Association key => value
var_dump($cnx->sqlAssoc("SELECT Channel, Name FROM TVSeries WHERE Id = ?", 3));

// Association key => associative array
var_dump($cnx->sqlAssoc("SELECT Channel, Id, Name FROM TVSeries WHERE Id = ?", 3, PDOExtended::TO_ARRAY_ASSOC));

// Association key => indexed array
var_dump($cnx->sqlAssoc("SELECT Channel, Id, Name FROM TVSeries WHERE Id = ?", 3, PDOExtended::TO_ARRAY_INDEX));

// Association key => stdClass
var_dump($cnx->sqlAssoc("SELECT Channel, Id, Name FROM TVSeries WHERE Id = ?", 3, PDOExtended::TO_STDCLASS));

// Association key => value, multiline version (array of keys => values)
var_dump($cnx->sqlMultiAssoc("SELECT Channel, Name FROM TVSeries WHERE Id IN (?, ?)", Array(1, 2)));

// Association key => associative array, multiline version (array of keys => associative arrays)
var_dump($cnx->sqlMultiAssoc("SELECT Channel, Id, Name FROM TVSeries WHERE Id IN (?, ?)", Array(1, 2), PDOExtended::TO_ARRAY_ASSOC));

// Association key => indexed array, multiline version (array of keys => indexed arrays)
var_dump($cnx->sqlMultiAssoc("SELECT Channel, Id, Name FROM TVSeries WHERE Id IN (?, ?)", Array(1, 2), PDOExtended::TO_ARRAY_INDEX));

// Association key => stdClass, multiline version (array of keys => stdClasses)
var_dump($cnx->sqlMultiAssoc("SELECT Channel, Id, Name FROM TVSeries WHERE Id IN (?, ?)", Array(1, 2), PDOExtended::TO_STDCLASS));

// You can also invoke all theses methods from a PDOStatementExtended object
var_dump($cnx->prepare("SELECT Channel, Id, Name FROM TVSeries WHERE Id IN (?, ?)")->sqlMultiAssoc(Array(2, 3), PDOExtended::TO_STDCLASS));

// How long the query has taken ?
$stmt   =   $cnx->prepare("SELECT * FROM TVSeries WHERE Id = :Id OR Name LIKE :Name");
$res    =   $stmt->sqlArray(Array('Id' => 1, 'Name' => 'Dexter'));
var_dump($stmt->getDuration());

// What was the real query played ?
var_dump($stmt->debug()->preview());

// You can disconnect : every call afterwards will result in a PDO Exception until you invoke the Reconnect() method
$cnx->disconnect();

// You can also Pause the connection. It actually disconnects from MySQl, but on the next call (sql, query, sqlArray etc) the connect() method will automatically be called so you never have a "not connected" exception thrown
$cnx->pause();

// Why doing this ? When you have a big treatment to do (parsing a big xml for instance), this prevents from getting "sleep connections" issues


// Every other PDO method is available... $cnx->query() ou $cnx->setAttribute(), etc.

Installation
------------
Add the following line into your composer.json :

    {
        "require": {
            "bentools/pdoextended": "dev-master"
        }
    }  
    
Enjoy.