Return to Answer

try { $pdo->beginTransaction(); foreach ($queries as $one) { $stmt->execute($row); } $pdo->commit(); }catch (Exception $e){ $pdo->rollback(); throw $e; }

try {
    $conn = $this->connection($db2use, $conf = []);
    $conn->beginTransaction();
    foreach ($queries as $one)
    {
        $stmt = $conn->prepare($one['query']);
        foreach ($one['params'] as $params)
            $stmt->execute($params);
        }
    }
    $conn->commit();
}catch (Exception $e){
    $pdo->rollback();
    throw $e;
}

To perform multuple queries in a transaction, have a distinct method that accepts an array of arrays each of the following structure

$queries = [
    [
        'query' => "INSERT INTO table1 VALUES (null, ?,?,?)";
        'params' => [
                        [$name, $position, $salary],
                    ],
    ],
    [
        'query' => "UPDATE table2 SET category=? where id=?";
        'params' => [
                        [$category1, $id1],
                        [$category2, $id2],
                        [$category3, $id3],
                    ],
    ],
];

then you will be able to loop over queries and for each it will be possible to execute multiple sets of parameters, like this (based on the code from my multiple query execution example)

try { $pdo->beginTransaction(); foreach ($queries as $one) { $stmt->execute($row); } $pdo->commit(); }catch (Exception $e){ $pdo->rollback(); throw $e; }

For some of your class' issues I have a review already. Some time ago I wrote an article about common mistakes in database wrappers.

To list them briefly,

• the most grave problem, no support for prepared statements. In 2019 it cannot be excused. It's like starting your own bank and storing the money on the backyard. You will go bankrupt yourself and ruin your clients' money. Your query array must accept a query with placeholders and an array with data for them.
• error reporting. echo var_dump($e); is definitely not the way errors should be reported. Instead of catching PDO exceptions just leave them be.
• Connection is made every time anew which is also bad. If you have to work wth different connections, then write a distinct method that creates different connections on demand and holds them during the script execution, returning the same connection every time it is requested.

There are other issues as well.

The most evident one is the code duplication, on so many levels

• The code in InsertData, UpdateData, DeleteData is identical. Why use that many functions if you can use just one?
• And even if you want separate functions, why duplicate so much code every time? Create a generic method that contains all the common code and then call it inside other methods.
• and some code is duplicated within functions without any apparent reason. Why you are calling the SET NAMES query so many times? the charset should be set only once (and it must be done in the DSN, not in the query call).

The code in the catch part for a transaction looks VERY strange. I suppose it is written out of some incorrect notion. There is not a single reason to reset the autoincrement, least it should be done for a failed transaction. Make your code for catch just this way

   } catch (PDOException $e) {
        $conn->rollback();
        throw $e;
   }

and leave autoincrement alone.

the way you are using GetData() is rather strange. Why you are selecting the first row from the resultset? What if your code would expect several rows to be iterated over, but for the moment only one returned?

Let me suggest another code for this function

public function getData($query, $data = [], $db2use = '', $Conf = [])
{
        // all the connection related stuff  should go into a dedicated method
        $conn = $this->connection($db2use, $Conf = []);
        $stmt = $conn->prepare($query);
        $stmt->execute($data);
        return $stmt;
}

with this simple function you will be able to get everything you want from the query and even more. for example,

$query  = "SELECT user FROM table_user WHERE unsername=?";
$user = $this->DBMANAGER->GetData($query, [$username],'DatabaseName')->fetchColumn();

the code is much simpler, both in the function and in the application and at the same time it is more flexible, for example it lets you use various fetch methods offered by PDO

For some of your class' issues I have a review already. Some time ago I wrote an article about common mistakes in database wrappers.

To list them briefly,

• the most grave problem, no support for prepared statements. In 2019 it cannot be excused. It's like starting your own bank and storing the money on the backyard. You will go bankrupt yourself and ruin your clients' money. Your query array must accept a query with placeholders and an array with data for them.
• error reporting. echo var_dump($e); is definitely not the way errors should be reported. Instead of catching PDO exceptions just leave them be.
• Connection is made every time anew which is also bad. If you have to work wth different connections, then write a distinct method that creates different connections on demand and holds them during the script execution, returning the same connection every time it is requested.

There are other issues as well.

The most evident one is the code duplication, on so many levels

• The code in InsertData, UpdateData, DeleteData is identical. Why use that many functions if you can use just one?
• And even if you want separate functions, why duplicate so much code every time? Create a generic method that contains all the common code and then call it inside other methods.
• and some code is duplicated within functions without any apparent reason. Why you are calling the SET NAMES query so many times? the charset should be set only once (and it must be done in the DSN, not in the query call).

The code in the catch part for a transaction looks VERY strange. I suppose it is written out of some incorrect notion. There is not a single reason to reset the autoincrement, least it should be done for a failed transaction. Make your code for catch just this way

   } catch (PDOException $e) {
        $conn->rollback();
        throw $e;
   }

and leave autoincrement alone.

the way you are using GetData() is rather strange. Why you are selecting the first row from the resultset? What if your code would expect several rows to be iterated over, but for the moment only one returned?

Let me suggest another code for this function

public function getData($db2use, $query, $data = [], $Conf = [])
{
        // all the connection related stuff  should go into a dedicated method
        $conn = $this->connection($db2use, $conf = []);
        $stmt = $conn->prepare($query);
        $stmt->execute($data);
        return $stmt;
}

with this simple function you will be able to get everything you want from the query and even more. for example,

$query  = "SELECT user FROM table_user WHERE unsername=?";
$user = $this->db->GetData('DatabaseName', $query, [$username])->fetchColumn();

the code is much simpler, both in the function and in the application and at the same time it is more flexible, for example it lets you use various fetch methods offered by PDO