PDO library to handle MySQL queries

Question

This is my library class. This does not have any issues but I want to know if I have any mistakes, or ways to improve performance:

variable and call (example of SELECT):

include(class_dbmanager.php)
$this->DBMANAGER  = new Class_DbManager();
$Query['DatabaseName']  = "SELECT user FROM table_user WHERE unsername='" . $username . "';";
$BDResult      = $this->DBMANAGER->GetData($Query);

variable and call (example of INSERT/UPDATE):

$Query['DatabaseName'][] = "INSERT INTO Tbl_Sys_Usuarios(IdTUser, Username, Password, Email) VALUES ('$IdTUser', '$Username', '$Password', '$Email');";
$BDResult       = $this->DBMANAGER->InsertData($Query);

The library:

<?php
class Class_DbManager {
    //for select
    //$Query is array with database index and query string
    //$Conf is secundary conection data
    public function GetData($Query, $Conf = '') {
        try {
            $DB_R   = [];
            $DB     = [];
            $val    = [];
            $prefix = '';
            $count  = 0;
            if (USEPREFIX == True) {
                $prefix = DB_PRE; //prefix DB
            }
            reset($Query);
            $DB_2USE = key($Query);
            //Conecction. i use defined const...
            $conn    = new PDO("mysql:host=" . DB_HOST . ";dbname=" . $prefix . "" . DBSYS . "", DB_USER, DB_PASS);
            //secundary Connection
            if (isset($Conf['CONF']['ChangeServ'])) {
                if ($Conf['CONF']['ChangeServ'] == true) {
                    $conn = new PDO("mysql:host=" . $Conf['CONF']['DB_HOST'] . ";dbname=" . $Conf['CONF']['PREFIX2USE'] . "" . $Conf['CONF']['DB2USE'] . "", $Query['CONF']['DB_USER'], $Query['CONF']['DB_PASS']);
                }
            }
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->exec("set names utf8");
            $conn->exec('USE ' . $DB_2USE);
            //execution query.
            $DB_R['r'] = $conn->query($Query[$DB_2USE], PDO::FETCH_ASSOC);
            $count     = $DB_R['r']->rowCount();
            $DB_R['c'] = $count;
            if ($count == 0) {
                $DB_R['r'] = null;
            } elseif ($count == 1) {
                $DB_R['r'] = $DB_R['r']->fetch(); //Fetch result i f result is 1 if not resturn the result unfetch
            }
            $conn = null;
            return $DB_R;
        } catch (PDOException $e) {
            echo '<pre>';
            echo var_dump($e);
            echo '<pre>';
        }
    }
    //for Update and Insert
    //$Query is array with database index and query string
    //$Conf is secundary conection data
    public function UpdateData($Query, $Conf = '') {
        try {
            $DB_R   = [];
            $DB     = [];
            $val    = [];
            $prefix = '';
            $cT     = 0;
            if (USEPREFIX == True) {
                $prefix = DB_PRE;
            }
            $conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . $prefix . "" . DBSYS . "", DB_USER, DB_PASS);
            if (isset($Conf['CONF']['ChangeServ'])) {
                if ($Conf['CONF']['ChangeServ'] == true) {
                    $conn = new PDO("mysql:host=" . $Conf['CONF']['DB_HOST'] . ";dbname=" . $Conf['CONF']['PREFIX2USE'] . "" . $Conf['CONF']['DB2USE'] . "", $Query['CONF']['DB_USER'], $Query['CONF']['DB_PASS']);
                }
            }
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            foreach ($Query as $DB_2USE => $QArr) {
                $conn->exec('USE ' . $DB_2USE);
                foreach ($QArr as $key => $QString) {
                    $conn->exec($QString);
                    $cT++;
                }
            }
            $conn->commit();
            $conn      = null;
            $DB_R['r'] = true;
            return $DB_R;
        } catch (PDOException $e) {
            #rollback al autoincrement
            $conn->rollback();
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            foreach ($Query as $DB_2USE => $QArr) {
                $conn->exec('USE ' . $DB_2USE);
                foreach ($QArr as $key => $QString) {
                    preg_match('/\binto\b\s*(\w+)/i', $QString, $tables);
                    $conn->exec("ALTER TABLE " . $tables[1] . " AUTO_INCREMENT=1;");
                }
            }
            $conn->commit();
            echo '<pre>';
            echo var_dump($e);
            echo '<pre>';
        }
    }
    //for Insert
    //$Query is array with database index and query string
    //$Conf is secundary conection data
    public function InsertData($Query, $Conf = '') {
        try {
            $DB_R   = [];
            $DB     = [];
            $val    = [];
            $prefix = '';
            $cT     = 0;
            if (USEPREFIX == True) {
                $prefix = DB_PRE;
            }
            $conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . $prefix . "" . DBSYS . "", DB_USER, DB_PASS);
            if (isset($Conf['CONF']['ChangeServ'])) {
                if ($Conf['CONF']['ChangeServ'] == true) {
                    $conn = new PDO("mysql:host=" . $Conf['CONF']['DB_HOST'] . ";dbname=" . $Conf['CONF']['PREFIX2USE'] . "" . $Conf['CONF']['DB2USE'] . "", $Query['CONF']['DB_USER'], $Query['CONF']['DB_PASS']);
                }
            }
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            foreach ($Query as $DB_2USE => $QArr) {
                $conn->exec('USE ' . $DB_2USE);
                foreach ($QArr as $key => $QString) {
                    $conn->exec($QString);
                    $cT++;
                }
            }
            $conn->commit();
            $conn      = null;
            $DB_R['r'] = true;
            return $DB_R;
        } catch (PDOException $e) {
            #rollback al autoincrement
            $conn->rollback();
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            foreach ($Query as $DB_2USE => $QArr) {
                $conn->exec('USE ' . $DB_2USE);
                foreach ($QArr as $key => $QString) {
                    preg_match('/\binto\b\s*(\w+)/i', $QString, $tables);
                    $conn->exec("ALTER TABLE " . $tables[1] . " AUTO_INCREMENT=1;");
                }
            }
            $conn->commit();
            echo '<pre>';
            echo var_dump($e);
            echo '<pre>';
        }
    }
    //for delete
    //$Query is array with database index and query string
    //$Conf is secundary conection data
    public function DeleteData($Query, $Conf = '') {
        try {
            $DB_R   = [];
            $DB     = [];
            $val    = [];
            $prefix = '';
            $cT     = 0;
            if (USEPREFIX == True) {
                $prefix = DB_PRE;
            }
            $conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . $prefix . "" . DBSYS . "", DB_USER, DB_PASS);
            if (isset($Conf['CONF']['ChangeServ'])) {
                if ($Conf['CONF']['ChangeServ'] == true) {
                    $conn = new PDO("mysql:host=" . $Conf['CONF']['DB_HOST'] . ";dbname=" . $Conf['CONF']['PREFIX2USE'] . "" . $Conf['CONF']['DB2USE'] . "", $Query['CONF']['DB_USER'], $Query['CONF']['DB_PASS']);
                }
            }
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            foreach ($Query as $DB_2USE => $QArr) {
                $conn->exec('USE ' . $DB_2USE);
                foreach ($QArr as $key => $QString) {
                    $conn->exec($QString);
                    $cT++;
                }
            }
            $conn->commit();
            $conn      = null;
            $DB_R['r'] = true;
            return $DB_R;
        } catch (PDOException $e) {
            #rollback al autoincrement
            $conn->rollback();
            $conn->beginTransaction();
            $conn->exec("set names utf8");
            foreach ($Query as $DB_2USE => $QArr) {
                $conn->exec('USE ' . $DB_2USE);
                foreach ($QArr as $key => $QString) {
                    preg_match('/\binto\b\s*(\w+)/i', $QString, $tables);
                    $conn->exec("ALTER TABLE " . $tables[1] . " AUTO_INCREMENT=1;");
                }
            }
            $conn->commit();
            echo '<pre>';
            echo var_dump($e);
            echo '<pre>';
        }
    }
}

i think add the compatibility for prepared statement but have this issues building the query string:

<?php
//perm analisis simulated create array.
$queryArr       = [];
$perms          = [];
$perms['p-001'] = array(0, 1, 1, 1, 0);
$perms['p-002'] = array(0, 1, 1, 0, 1);
$perms['p-003'] = array(1, 1, 0, 1, 1);
$perms['p-004'] = array(1, 0, 1, 1, 1);
//query String build
foreach ($perms as $index => $arrayperms) {
    if ($arrayperms[0] == 1) {
        //insert
        $query = "INSERT INTO tb_perms_user(Id_Proccess,create,read,update,delete)value('$index'," . $arrayperms[1] . "," . $arrayperms[2] . "," . $arrayperms[3] . "," . $arrayperms[4] . ");";
    } else {
        //update
        $query = "UPDATE tb_perms_user SET create=" . $arrayperms[1] . ",read=" . $arrayperms[2] . ",update=" . $arrayperms[3] . ",delete=" . $arrayperms[1] . " WHERE Id_Proccess='$index';";
    }
    $queryArr['databasename'][] = $query;
}

echo '<pre>';
echo var_dump($queryArr);
echo '</pre>';

When i run the script like this:

$BDResult       = $this->DBMANAGER->InsertData($queryArr);

Answer 1

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

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 {
    $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;
}