Home / Questions / 8197
Accepted
dddenis
Asked: 2020-04-22 01:18:01 +0800 CST 2020-04-22 01:18:01 +0800 CST

'Dynamic' method to perform update transactions with PDO

  • 772

I am carrying out a web project on a local Agency, in which many transactions are carried out, and I am using PDO to manage access to the MySQL database.

The DB class is statically responsible for providing access to insert, select, update, delete queries, procedures and transactions, but the latter does not work as expected and executes the last prepared statement with its parameters.

I'm trying to get it to receive an array of prepared queries as strings, and an array of arrays of the parameters matching in position to each prepared query.

The transaction($sqls, $parameters) method of the DB class failing:

public static function transaction($sqls, $parameteres) { // TODO: falla.
    try {
        DB::connect()->beginTransaction();
        foreach (array_combine($sqls, $parameteres) as $sql => $params) {
            $rs = DB::connect()->prepare($sql);
            $rs->execute($params);
        }
        DB::connect()->commit();
        return true;
    } catch (PDOException $e) {
        print_r($e);
        DB::connect()->rollBack();
        return false;
    }
}

db_config.php

<?php

define("DRIVER_SQL", "mysql");
define("DATABASE", "tests_db");
define("PORT",     "8889");
define("HOSTNAME", "localhost");
define("USERNAME", "root");
define("PASSWORD", "root");


define("URI", DRIVER_SQL . ":dbname=" . DATABASE . ";port=" . PORT . ";host=" . HOSTNAME);

?>

DB.php

<?php

ini_set('display_errors', 1);
require_once('db_config.php');

final class DB {

// STATIC FIELDS....................................................................................................

private static $pdo; // database connection

// SPECIAL METHODS..................................................................................................

final private function __construct() {
    // empty constructor
}

final private function __clone() {
    throw new Exception("You can't clone this object because is unique.");
}

function _destructor() {
    // clean pointer, close database connection
    self::$pdo = null;
}

private static function connect() {
    try {
        if (self::$pdo != null) return self::$pdo;
        self::$pdo = new PDO(URI, USERNAME, PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
        self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        self::$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        self::$pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        self::$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
        return self::$pdo;
    }
    catch (PDOException $e) {
        echo("PDOException: ".$e);
        return false;
    }
}

// STATIC METHODS...................................................................................................

public static function insert($sql, $parameters) {
    return self::_query($sql, $parameters);
}

public static function select($sql, $parameters=null) {
    try {
        $rs = DB::connect()->prepare($sql);
        $rs->execute($parameters);
        return $rs->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        print_r($e);
        return null;
    }
}

public static function update($sql, $parameters) {
    return self::_query($sql, $parameters);
}

public static function delete($sql, $parameters) {
    return self::_query($sql, $parameters);
}

public static function transaction($sqls, $parameteres) { // TODO: falla.
    try {
        DB::connect()->beginTransaction();
        foreach (array_combine($sqls, $parameteres) as $sql => $params) {
            $rs = DB::connect()->prepare($sql);
            $rs->execute($params);
        }
        DB::connect()->commit();
        return true;
    } catch (PDOException $e) {
        print_r($e);
        DB::connect()->rollBack();
        return false;
    }
}

public static function procedure($sql, $parameters = null) { // TODO: Por testear
    return self::_query($sql, $parameters);
}

private static function _query($sql, $parameters) {
    try {
        $rs = DB::connect()->prepare($sql);
        return $rs->execute($parameters);
    } catch (PDOException $e) {
        print_r($e);
        return null;
    }
}

}

?>

To perform the tests I used this code:

<?php

ini_set('display_errors', 1);
include_once 'db.php';

// TABLA: id, name, surname, age

$sql1 = "INSERT INTO person VALUES (NULL, ?, ?, ?)";
$sql2 = "INSERT INTO person VALUES (NULL, ?, ?, ?)";
$params1 = array('nombre_1', 'apellido_1', '1');
$params2 = array('nombre_2', 'apellido_2', '2');

$sqls = array($sql1,$sql2);
$parameters = array($params1,$params2);

DB::transaction($sqls,$parameters);

foreach (DB::select("SELECT * FROM person") as $row) {
    echo($row['name'] . " " .$row['surname'] . "<br/>");
}

?>
php

1 Answers

  1. Best Answer

    The problem is that $sql1and $sql2are the same, so when making array_combine $sql1and $sql2become the key of the array they are joined into one because they cannot be repeated,

    If you run this:

    print_r(array_combine($sqls, $parameteres));

    You will see that your result is:

    Array
(
    [INSERT INTO person VALUES (NULL, ?, ?, ?)] => Array
        (
            [0] => nombre_2
            [1] => apellido_2
            [2] => 2
        )

)

    It is the same case as described in the PHP documentation

    <?php
print_r(array_combine(Array('a','a','b'), Array(1,2,3)));
?>
Returns:
Array
(
    [a] => 2
    [b] => 3
)

    Therefore, you could modify your transaction method and leave it like this:

    public static function transaction($sqls, $parameteres) { // TODO: falla.
    try {
        DB::connect()->beginTransaction();
        foreach( $sqls as $index => $sql ) {
            $rs = DB::connect()->prepare($sql);
            $rs->execute($parameteres[$index]);
        }
        DB::connect()->commit();
        return true;
    } catch (PDOException $e) {
        print_r($e);
        DB::connect()->rollBack();
        return false;
    }
}
    • 5