PHP code example of rawsrc / pdoplusplus

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

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

    

rawsrc / pdoplusplus example snippets

// first profile: power user
PDOPlusPlus::addCnxParams(
    cnx_id: 'user_root',
    params: [
        'scheme' => 'mysql',
        'host' => 'localhost',
        'database' => '',
        'user' => 'root',
        'pwd' => '**********',
        'port' => '3306',
        'timeout' => '5',
        'pdo_params' => [],
        'dsn_params' => []
    ],
    is_default: true
);
// second profile: basic user
PDOPlusPlus::addCnxParams(
    cnx_id: 'user_test',
    params: [
        'scheme' => 'mysql',
        'host' => 'localhost',
        'database' => 'db_pdo_plus_plus',
        'user' => 'user_test',
        'pwd' => '**********',
        'port' => '3306',
        'timeout' => '5',
        'pdo_params' => [],
        'dsn_params' => []
    ],
    is_default: false
);

$data = [[
    'title'           => "The Lord of the Rings - The Fellowship of the Ring",
    'support'         => 'BLU-RAY',
    'multilingual'    => true,
    'chapter'         => 1,
    'year'            => 2001,
    'summary'         => null,
    'stock'           => 10,
    'bigint_unsigned' => '18446744073709551600',
    'bigint_signed'   => -9223372036854775000,
], [
    'title'           => "The Lord of the Rings - The two towers",
    'support'         => 'BLU-RAY',
    'multilingual'    => true,
    'chapter'         => 2,
    'year'            => 2002,
    'summary'         => null,
    'stock'           => 0,
    'bigint_unsigned' => '18446744073709551600',
    'bigint_signed'   => -9223372036854775000,
], [
    'title'           => "The Lord of the Rings - The return of the King",
    'support'         => 'DVD',
    'multilingual'    => true,
    'chapter'         => 3,
    'year'            => 2003,
    'summary'         => null,
    'stock'           => 1,
    'bigint_unsigned' => '18446744073709551600',
    'bigint_signed'   => -9223372036854775000,
]];

$ppp = new PDOPlusPlus(); // here the default connection wil be used and the auto-reset is enabled
$film = $data[0];
$sql = <<<sql
INSERT INTO t_video (
    video_title, video_support, video_multilingual, video_chapter, video_year, 
    video_summary, video_stock, video_bigint_unsigned, video_bigint_signed
) VALUES (
    {$ppp($film['title'])}, {$ppp($film['support'])}, {$ppp($film['multilingual'], 'bool')},
    {$ppp($film['chapter'], 'int')}, {$ppp($film['year'], 'int')}, {$ppp($film['summary'])}, 
    {$ppp($film['stock'], 'int')}, {$ppp($film['bigint_unsigned'], 'bigint')}, 
    {$ppp($film['bigint_signed'], 'bigint')}
)
sql;
$new_id = $ppp->insert($sql);   // $new_id = '1'

$in = $ppp->getInjectorInByVal();
$film = $data[1];
$sql = <<<sql
INSERT INTO t_video (
    video_title, video_support, video_multilingual, video_chapter, video_year, 
    video_summary, video_stock, video_bigint_unsigned, video_bigint_signed
) VALUES (
    {$in($film['title'])}, {$in($film['support'])}, {$in($film['multilingual'], 'bool')},
    {$in($film['chapter'], 'int')}, {$in($film['year'], 'int')}, {$in($film['summary'])}, 
    {$in($film['stock'], 'int')}, {$in($film['bigint_unsigned'], 'bigint')}, 
    {$in($film['bigint_signed'], 'bigint')}
)
sql;
$new_id = $ppp->insert($sql);   // $new_id = '2' 

$ppp->execute('TRUNCATE TABLE t_video');

$in = $ppp->getInjectorInByRef(); 
$sql = <<<sql
INSERT INTO t_video (
    video_title, video_support, video_multilingual, video_chapter, video_year, 
    video_summary, video_stock, video_bigint_unsigned, video_bigint_signed
) VALUES (
    {$in($title)}, {$in($support)}, {$in($multilingual, 'bool')}, {$in($chapter, 'int')}, {$in($year, 'int')}, 
    {$in($summary)}, {$in($stock, 'int')}, {$in($bigint_unsigned, 'bigint')}, {$in($bigint_signed, 'bigint')}
)
sql;
foreach ($data as $film) {
    extract($film); // destructuring the array into components used to populate the references declared just above
    $ppp->insert($sql); 
}

// we clean the instance
$ppp->reset();

$id = 1;
$support = 'DVD';
$sql = "UPDATE t_video SET video_support = {$ppp($support)} WHERE video_id = {$ppp($id, 'int')}";
$nb = $ppp->update($sql);  // nb of affected rows

$id = 1;
$sql = "DELETE FROM t_video WHERE video_id = {$ppp($id, 'int')}";
$nb = $ppp->delete($sql); // nb of affected rows

$id = 1;
$sql = "SELECT * FROM t_video WHERE video_id = {$ppp($id, 'int')}";
$data = $ppp->select($sql);

$sql  = "SELECT * FROM t_video WHERE video_support LIKE {$ppp('%RAY%')}";
$data = $ppp->select($sql);

$sql  = "SELECT * FROM t_video WHERE video_support LIKE {$ppp('%RAY%')}";
$stmt = $ppp->selectStmt($sql);
$data = $stmt->fetchAll(PDO::FETCH_OBJ);

$sql = "SELECT * FROM t_video WHERE video_support LIKE {$ppp('%RAY%')}";
$stmt = $ppp->selectStmtAsScrollableCursor($sql);
while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
    // ... // 
}

// First, you have to prepare the bound variables.
$columns = [
    'video_title' => [&$video_title, 'str'], // watch carefully the & before the var
    'video_img' => [&$video_img, 'binary'], // watch carefully the & before the var
];

// you have to declare into the instance the bound columns
$ppp->setBoundColumns($columns);

// then call the selectStmt()
$ppp->selectStmt("SELECT video_title, video_img FROM t_video WHERE video_id = {$ppp(1, 'int')}");
// then read the result
while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {
    // here $video_title and $video_img are available and well defined 
}

$ppp = new PPP();
$exec = $ppp->execute(<<<'sql'
CREATE OR REPLACE DEFINER = root@localhost PROCEDURE db_pdo_plus_plus.sp_list_films()
BEGIN
    SELECT * FROM t_video;
END;
sql
);

$rows = $ppp->call('CALL sp_list_films()', true);   // the true tells PPP that SP is a query
// $rows is a multidimensional array: 
// $rows[0] => for the first dataset which is an array of all films  

// TWO ROWSET
$exec = $ppp->execute(<<<'sql'
CREATE OR REPLACE DEFINER = root@localhost PROCEDURE db_pdo_plus_plus.sp_list_films_group_by_support()
BEGIN
    SELECT * FROM t_video WHERE video_support = 'BLU-RAY';
    SELECT * FROM t_video WHERE video_support = 'DVD';
END;
sql
);

$rows = $ppp->call('CALL sp_list_films_group_by_support()', true); // the true tells PPP that SP is a query
// $rows is a multidimensional array: 
// $rows[0] => for the first dataset which is an array of films (BLU-RAY) 
// $rows[1] => for the second dataset which is an array of films (DVD)

// WITH ONE IN PARAM
$exec = $ppp->execute(<<<'sql'
CREATE OR REPLACE DEFINER = root@localhost PROCEDURE db_pdo_plus_plus.sp_list_films_one_in_param(
    p_support VARCHAR(30)
)
BEGIN
    SELECT * FROM t_video WHERE video_support = p_support;
END;
sql
);

// AND CALL IT
// FIRST METHOD : plain sql
$rows = $ppp->call("CALL sp_list_films_one_in_param({$ppp('DVD')})", true);
// $rows is a multidimensional array: 
// $rows[0] => for the first dataset which is an array of films (DVD)

// EXACTLY THE SAME USING ->bindValue()
$in = $ppp->getInjectorInByVal();
$rows = $ppp->call("CALL sp_list_films_one_in_param({$in('DVD')})", true);

// AND IF YOU WANT TO USE A REFERENCE INSTEAD
$in   = $ppp->getInjectorInByRef();
$sup  = 'DVD';
$rows = $ppp->call("CALL sp_list_films_one_in_param({$in($sup)})", true);
$ppp->reset(); // do not forget to reset the instance to be able to reuse it 

// WITH ONE OUT PARAM
$exec = $ppp->execute(<<<'sql'
CREATE OR REPLACE DEFINER = root@localhost PROCEDURE db_pdo_plus_plus.sp_nb_films_one_out_param(
    OUT p_nb INT
)
BEGIN
    SELECT COUNT(video_id) INTO p_nb FROM t_video;
END;
sql
);

$out = $ppp->getInjectorOut();
$exec = $ppp->call("CALL sp_nb_films_one_out_param({$out('@nb')})", false);
$nb = $exec['out']['@nb'];

// WITH ROWSET AND TWO OUT PARAM
$exec = $ppp->execute(<<<'sql'
CREATE OR REPLACE DEFINER = root@localhost PROCEDURE db_pdo_plus_plus.sp_nb_films_rowset_two_out_param(
    OUT p_nb_blu_ray INT, 
    OUT p_nb_dvd INT
)
BEGIN
    SELECT * FROM t_video ORDER BY video_year DESC;
    SELECT COUNT(video_id) INTO p_nb_blu_ray FROM t_video WHERE video_support = 'BLU-RAY';
    SELECT COUNT(video_id) INTO p_nb_dvd FROM t_video WHERE video_support = 'DVD';
END;
sql
);

$out = $ppp->getInjectorOut();
$exec = $ppp->call("CALL sp_nb_films_rowset_two_out_param({$out('@nb_blu_ray')}, {$out('@nb_dvd')})", true);
$rows = $exec[0];  // $exec[0] => for the first dataset which is an array of all films ordered by year DESC
$nb_br = $exec['out']['@nb_blu_ray']; // note the key 'out'
$nb_dv = $exec['out']['@nb_dvd'];

// WITH ONE INOUT PARAM AND TWO OUT PARAM
$exec = $ppp->execute(<<<'sql'
CREATE OR REPLACE DEFINER = root@localhost PROCEDURE db_pdo_plus_plus.sp_nb_films_one_inout_two_out_param(
    INOUT p_qty INT, 
    OUT p_nb_blu_ray INT, 
    OUT p_nb_dvd INT
)
BEGIN
    DECLARE v_nb INT;
    SELECT SUM(video_stock) INTO v_nb FROM t_video;
    SET p_qty = v_nb - p_qty;
    SELECT COUNT(video_id) INTO p_nb_blu_ray FROM t_video WHERE video_support = 'BLU-RAY';
    SELECT COUNT(video_id) INTO p_nb_dvd FROM t_video WHERE video_support = 'DVD';
END;
sql
);

$io = $ppp->getInjectorInOut();       // io => input/output
$out = $ppp->getInjectorOut();
$exec = $ppp->call("CALL sp_nb_films_one_inout_two_out_param({$io('25', '@stock', 'int')}, {$out('@nb_blu_ray')}, {$out('@nb_dvd')})", false);
$stock = $exec['out']['@stock'];
$nb_br = $exec['out']['@nb_blu_ray'];
$nb_dv = $exec['out']['@nb_dvd'];

// Exception wrapper for PDO
PDOPlusPlus::setExceptionWrapper(function(Exception $e, PDOPlusPlus $ppp, string $sql, string $func_name, ...$args) {
    // here you code whatever you want
    // ...
    // then you must return a result
    return 'DB Error, unable to execute the query';
});

try {
    $ppp = new PDOPlusPlus();
    $sql = "INSERT INTO t_table (field_a, field_b) VALUES ({$ppp('value_a')}, {$ppp('value_b')})";
    $id  = $ppp->insert($sql);
} catch (Exception $e) {
    // bla bla
}

$ppp = new PDOPlusPlus();
$ppp->setThrowOff();
$sql = "INSERT INTO t_table (field_a, field_b) VALUES ({$ppp('value_a')}, {$ppp('value_b')})";
$id  = $ppp->insert($sql);
if ($id === null) {
    $error = $ppp->getErrorFromWrapper(); // $error = 'DB Error, unable to execute the query'
}