PHP code example of rain1 / pdo-powered

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

rain1 / pdo-powered example snippets

use \rain1\PDOPowered\Config\Config;
$config = new Config("mysql", "user", "password", "localhost", 3306, "dbname", "utf8", [\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'']);

use \rain1\PDOPowered\Config\DSN;
$config = new DSN(
    "mysql:host=localhost;port=3306;dbname=dbname;charset=utf8", "user", "password"
);
$config->setOptions([\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"]);

$db = new PDOPowered($config);

$db = PDOPowered::buildFromPDOInstance($pdoInstance);

// fast insert. Return the inserted id
$id = $db->insert("tabletest", ['id'=>1, 'col1'=>'col1_1']);

// fast update
$db->update("tabletest", ['col1'=>'updated'], ['id'=>$id]);

// build a "INSERT ON DUPLICATE KEY UPDATE" query
$db->insertOnDuplicateKeyUpdate("tabletest", ['id'=> $id, 'col1'=>'updated']);
$db->insertOnDuplicateKeyUpdate("tabletest", ['id'=> $id+1, 'col1'=>'updated']);

// performs a delete
$db->delete("tabletest", ['id'=>$id]);

// simple query
$row = $db->query("SELECT * FROM tabletest WHERE id = ?", [$id])->fetch();

// or equivalent
$row = $db->query("SELECT * FROM tabletest WHERE id = :id", ['id'=>$id])->fetch();

// triggered after a connection fails: before the next attempt
$db->onConnectionFailure(function($connectionTry, \Exception $exception) {
    sleep($connectionTry);
    error_log("Unable to connect on mysql: " . $exception->getMessage());
});

// triggered after a connection success
$db->onConnect(function() {
    echo "Db connected\n";
});

// triggered on debug string
$idDebugListener = $db->onDebug(function($debugType, \PDOStatement $PDOStatement = null, ...$args) {
    echo "$debugType\n";
    if($PDOStatement)
        $PDOStatement->debugDumpParams();
    echo "\n".json_encode($args);
});

// for each event we can remove the listener
$db->removeDebugListener($idDebugListener);

$db->onDebug(DebugParser::onParse(function ($info) {
    print_r($info); // info for timing, query, params and finalized query
}));

// set maximum number of connection attempts.
\rain1\PDOPowered\Config\PDOPowered::$MAX_TRY_CONNECTION = 5;

// set pdo attribute after connection. 
$db->setPDOAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);

$users = $db->query(
    "SELECT * FROM user WHERE email = :email AND id = :id", 
    [
        "email" => new \rain1\PDOPowered\Param\ParamString("[email protected]"),
        "id"=> new \rain1\PDOPowered\Param\ParamInt("123"),
    ]
)->fetchAll();

[
    "param" => new \rain1\PDOPowered\Param\ParamNative("value", \PDO::PARAM_STR)
]

[
    "param" => new \rain1\PDOPowered\Param\ParamJSON(["key"=>"value"])
]

[
    "param" => json_encode(["key"=>"value"])
]

use rain1\PDOPowered\Config\Config;
use rain1\PDOPowered\Debug\DebugParser;
use rain1\PDOPowered\Param\ParamJSON;
use rain1\PDOPowered\Param\ParamString;
use rain1\PDOPowered\PDOPowered;

 = new Config(
    "mysql",
    "root",
    "vagrant",
    "localhost",
    3306,
    "test",
    "utf8",
    [
        \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    ]
);


$db = new PDOPowered($config);

output("adding listeners...");

$db->onConnectionFailure(function ($try, \Exception $e) {
    error_log("connection failed: " . $e->getMessage());
    output("connection failed");
});

$db->onConnect(function() {
    output("Connected", "");
});

// only for debug
$debug = 1;
$countQuery = 0;
$debug && $db->onDebug(
    DebugParser::onParse(
        function ($info) use (&$countQuery) {

            $str = "---- DEBUG ROW " . (++$countQuery) .  " ----\n";

            if (isset($info['query']))
                $str .= $info['query']['demo'] . "\nexecution time: " . $info['query']['executionTime'];
            else
                $str .= $info['type'] . " " . json_encode($info['args']);

            output($str, "");
        }
    )
);


output("set attributes");

$db->setPDOAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);

output("first query");

$db->query("DROP TABLE IF EXISTS user");

$db->query("create table user
(
	id int auto_increment
		primary key,
	email varchar(32) null,
	settings text null
)
; ");

$db->beginTransaction();
$id = $db->insert("user", ['email' => '[email protected]']);

$db->update("user", ["email" => "[email protected]"], ['id' => (int)$id]);

$db->delete("user", ['id' => 1]);

$db->insert("user", [
    'id' => 1,
    'email' => new ParamString("[email protected]")
]);

$db->insertOnDuplicateKeyUpdate("user", [
    'id' => 1,
    'email' => new ParamString("[email protected]"),
    'settings' => new ParamJSON(['privacy' => 1, 'newsletter' => 'no'])
]);

$rows = $db->query("SELECT * FROM user WHERE id = :id", ['id' => 1])->fetchAll();
$db->commitTransaction();

$db->query("SELECT SLEEP (1)");

print_r($rows);