1
\$\begingroup\$

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!

\$\endgroup\$

1 Answer 1

3
\$\begingroup\$

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.

\$\endgroup\$
11
  • 1
    \$\begingroup\$ it is not multiInsert that allows for different queries, but transaction(). You just put any code where the comment "// write any code you want to wrap into transaction" stays. It can be insert, update, delete or whatever in any combinations \$\endgroup\$ Jul 12, 2022 at 14:46
  • 1
    \$\begingroup\$ By the way, you don't have to limit yourself to just one question. Quite contrary, the more questions you ask, the better answer I can make. \$\endgroup\$ Jul 13, 2022 at 4:56
  • 1
    \$\begingroup\$ but didn't you already did that? Didn''t you just put all the code you need into updateRows function? So you already have it. Though you don't need that method but call transaction() just directly. Can you show your updateRows method online? like on phpize.online \$\endgroup\$ Jul 13, 2022 at 8:22
  • 1
    \$\begingroup\$ But you don't need a transaction for just a single query? \$\endgroup\$ Jul 13, 2022 at 8:38
  • 1
    \$\begingroup\$ It is not the question you worded unclearly, but the code you posted. There, for some reason, you have two transactions - one for insert and one for update. \$\endgroup\$ Jul 13, 2022 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.