Wrap INSERT statements to PDO transaction (PHP OOP)

Question

so Im trying to wrap SQL INSERT statements to PDO transaction in PHP OOP. I don't know if I'm doing it right and this is the best/easiest way to do it.

So what is important for me is:

There can be multiple(unpredictable) INSERT statement (which will be in loop in the final code) I want to wrap all of these queries to transaction and commit(execute) only at the end of the code (after loop in final code) And of course in PHP OOP.

My code looks like this right now:

class.inc.php

class MyQuery {
protected $DB_CONN;
private $DB_insert_query;

public function __construct($DB_CONN) {
    $this->PDO_CONN = $DB_CONN->DB_CONN();
}

public function insert_query($insert_query) {
    $this->DB_insert_query = $insert_query;
}

public function commit() {
    try {
        $this->PDO_CONN->beginTransaction();
        foreach ($this->DB_insert_query as $query) {
            $stmt = $this->PDO_CONN->prepare($query["query"]);
            $stmt->execute($query["params"]);
        }
        $this->PDO_CONN->commit();
    }
    catch (PDOException $e) {
        $this->PDO_CONN->rollBack();
        error_log("Error occurred. Error message: " . $e->getMessage() . ". File: " . $e->getFile() . ". Line: " . $e->getLine(), 0);
    }
    if ($stmt->rowCount()) {
        return true;
    } else {
        return false;
    }
}
}

insert.php

$DB_CONN = new DataBase;
$myQuery = new MyQuery($DB_CONN);

$insert_query[] = array("query" => "INSERT INTO mytable (name, text) VALUES (:name, :text);",
                        "params" => array(":name" => "Name Test", ":text" => "Text Test")
                         );

$myQuery->insert_query($insert_query);
                         
$insert_query[] = array("query" => "INSERT INTO mytable (name, text) VALUES (:name, :text);",
                        "params" => array(":name" => "Another Name", ":text" => "Another Text")
                         );

$myQuery->insert_query($insert_query);

print_r($myQuery->commit());

Thank you for your help!

Answer 1

I don't like the approach from the other answer as it violates the single responsibility principle. A function called insert_query shouldn't do things unrelated to, well, insert. And at the same time it is severely limiting your SQL. Let alone a straight up SQL injection.

But I understand the desire to encapsulate the regular transaction routine, i.e.

try {
    $pdo->beginTransaction();
    foreach ($data as $row)
    {
        $pdo->prepare($row['sql'])->execute($row['params']);
    }
    $pdo->commit();
}catch (\Throwable $e){
    $pdo->rollback();
    throw $e;
}

to make this code less boilerplate-looking. I would suggest the approach used in Laravel's Eloquent: a function that accepts anonymous function as a parameter. It gives you the separation concerns and enormous flexibility: not only insert queries are allowed but any kind of query or even PHP code inbetween.

So it could be like

class MyQuery
{
    public $dbConn;

    public function __construct($dbConn) {
        $this->dbConn = $dbConn;
    }

    public function query($query, $params) {
        $stmt = $this->dbConn->prepare($query);
        $stmt->execute($params);
        return $stmt;
    }

    public function transaction(Callable $f)
    {
        try {
            $this->dbConn->beginTransaction();
            $return = $f($this);
            $this->dbConn->commit();
            return $return;
        } catch (\Throwable $e) {
            $this->dbConn->rollBack();
            throw $e;
        }
    }
}

So it can be used like this

$myQuery->transaction( function () use (/* variables you need*/) {
    // write any code you want to wrap into transaction
});

and then using this generic transaction() method we can create a helper function for the specific multiple queries case

public function multiQueryTransaction($queries)
{
    $this->transaction( function () use ($queries) {
        foreach ($queries as $row)
        {
            $this->query($row['query'], $row['params']);
        }
    });
}

So the final code would be like

$insert_query[] = [
    "query" => "INSERT INTO mytable (name, text) VALUES (:name, :text);",
    "params" => [":name" => "Name Test", ":text" => "Text Test"],
];
$insert_query[] = [
    "query" => "INSERT INTO mytable (name, text) VALUES (:name, :text);",
    "params" => [":name" => "Another Name", ":text" => "Another Text"],
];

$pdo = new PDO ...;
$myQuery = new MyQuery($pdo);
$myQuery->multiQueryTransaction($insert_query);

Also, regarding the MyQuery() class in general, I would recommend to read this coding standard
and this article of mine, Your first database wrapper's childhood diseases that can explain some flaws and obsoleted parts in the existing code that I fixed in my version.