2
\$\begingroup\$

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);
\$\endgroup\$
  • 2
    \$\begingroup\$ I already have a review for the most of issues in your class. Some time ago I wrote an article about common mistakes in database wrappers. Sadly, but your library has almost all of them. \$\endgroup\$ – Your Common Sense May 21 at 19:56
  • \$\begingroup\$ @YourCommonSense Thanks, I will be doing a revision and adjustments to improve the library. \$\endgroup\$ – walter nuñez May 21 at 20:27
  • 1
    \$\begingroup\$ I'm not convinced that your example calls work, considering that they populate $Query in different ways. \$\endgroup\$ – 200_success May 21 at 21:12
  • 1
    \$\begingroup\$ @200_success $Query populate is diferent betwhen select to insert/Update/delete, becouse you only can execute a select query statement at a time, but i can pass much more that 1 query in insert/update/delete statement.... becouse it $query have 2 build method... single array for select, and 2 dimension array for insert/update/delete \$\endgroup\$ – walter nuñez May 21 at 21:16
  • \$\begingroup\$ What is the point of "ALTER TABLE " . $tables[1] . " AUTO_INCREMENT=1;" query? it looks extremely suspicious. Looks like you are shooting yourself in the foot here. Did you actually try this code on a real database? Was it able to work further afterwards? \$\endgroup\$ – Your Common Sense May 22 at 7:22
2
\$\begingroup\$

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;
}
\$\endgroup\$
  • \$\begingroup\$ thank you for response How would it be if I need to use internal junctions (inner joins) or nest select from different tables ... \$\endgroup\$ – walter nuñez May 22 at 13:40
  • \$\begingroup\$ I'm not very familiar with the prepared statement... zorry... \$\endgroup\$ – walter nuñez May 22 at 13:41
  • \$\begingroup\$ The result of the query with JOIN is exactly the same, a database class has nothing to do with it. And for the prepared statements there is nothing complicated, you may check it here, phpdelusions.net/pdo#prepared \$\endgroup\$ – Your Common Sense May 22 at 13:48
  • \$\begingroup\$ I have another question; this [$ username] is an array or what ?, I would like to pass a single data and not a very large structure [$username, $var1, $var2, $var3, $var4, $var5] \$\endgroup\$ – walter nuñez May 22 at 13:53
  • \$\begingroup\$ Yes, this is an array. Yes, you want to pass a structure if you want use all these variables in the query. There is nothing big, however. You are already sending all these variables in the query. So just send them separately. \$\endgroup\$ – Your Common Sense May 22 at 13:58

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.