Igäl
Erfahrenes Mitglied
Hallo Leute
Da ich an einem neuen Projekt sitze und meine alten Klassen wieder hervorgekramt habe, überprüfe ich sie momentan auf Aktualität. Vor allem, da seit der letzten Überarbeitung die eine oder andere PHP-Version ins Land gezogen ist. Deshalb meine Frage an die Cracks, ob meine DB-Handler-Klasse den aktuellen Gegebenheiten noch entspricht oder ob darin kapitale Böcke eingebaut sind.
Disclaimer: Es sind einige Zeilen Code. Meine Intention ist, dass jemand der LUST und gerade ein paar Minuten ZEIT hat, darüber schaut und mir gegebenenfalls mitteilt, wenn etwas absolut nicht mehr state of the art oder sogar sträflich fahrlässig ist, was da drin steht. Hier MUSS niemand und ich leb auch gut damit, wenn ich keine Antworten bekomme. Aber wenn ich nicht frage, habe ich garantiert keine Antworten. Deshalb der Post.
Es handelt sich um eine Klasse, die sämtliche Interaktionen mit der Datenbank regelt, die ich so regelmässig brauche. Falls jemand Inputs hat oder findet, dass ich für gewisse Zeilen geteert und gefedert gehöre... fair enough. Damit kann ich arbeiten
Also schonmal vorab vielen Dank für die, die sich die Mühe machen und ich versteh jeden, der sagt, dass er für so nen Scheiss keine Zeit hat
Edit: mysqli::execute_query() macht wohl Dreiviertel des Codes überflüssig.
Da ich an einem neuen Projekt sitze und meine alten Klassen wieder hervorgekramt habe, überprüfe ich sie momentan auf Aktualität. Vor allem, da seit der letzten Überarbeitung die eine oder andere PHP-Version ins Land gezogen ist. Deshalb meine Frage an die Cracks, ob meine DB-Handler-Klasse den aktuellen Gegebenheiten noch entspricht oder ob darin kapitale Böcke eingebaut sind.
Disclaimer: Es sind einige Zeilen Code. Meine Intention ist, dass jemand der LUST und gerade ein paar Minuten ZEIT hat, darüber schaut und mir gegebenenfalls mitteilt, wenn etwas absolut nicht mehr state of the art oder sogar sträflich fahrlässig ist, was da drin steht. Hier MUSS niemand und ich leb auch gut damit, wenn ich keine Antworten bekomme. Aber wenn ich nicht frage, habe ich garantiert keine Antworten. Deshalb der Post.
Es handelt sich um eine Klasse, die sämtliche Interaktionen mit der Datenbank regelt, die ich so regelmässig brauche. Falls jemand Inputs hat oder findet, dass ich für gewisse Zeilen geteert und gefedert gehöre... fair enough. Damit kann ich arbeiten
Also schonmal vorab vielen Dank für die, die sich die Mühe machen und ich versteh jeden, der sagt, dass er für so nen Scheiss keine Zeit hat
PHP:
<?php
/*********************************************************************************************************/
/** Author: Igäl **/
/** Date: 19.12.2020 **/
/** Last significant modification: 29.12.2020 **/
/** Release: 1.0, released 24.07.2011 **/
/*********************************************************************************************************/
/** This class contains needed functionality for script interaction with database **/
/*********************************************************************************************************/
class Mysql {
private $con = null;
private $fetch_multi = true;
/*********************************************************************************************************/
/** Constructor establishes a connection to mysqlserver and selects a specified database/charset **/
/*********************************************************************************************************/
public function __construct() {
$db_name = "dnd";
$db_host = $_SESSION['LiveServer'] === true ? "randomIP" : "localhost";
$db_user = $_SESSION['LiveServer'] === true ? "foo" : "bar";
$db_pass = $_SESSION['LiveServer'] === true ? "randomPassword" : "anotherRandomPassword";
$charset = "utf8";
$this->con = new mysqli($db_host, $db_user, $db_pass, $db_name);
if($this->con->connect_errno) {
trigger_error($this->con->connect_error, E_USER_ERROR);
$this->con = false;
}else{
$set_charset = $this->con->set_charset($charset);
if(!$set_charset)
trigger_error("Unable to set charset ".$charset." in Mysqli::set_charset", E_USER_WARNING);
}
}
/*********************************************************************************************************/
/** Destructor closes the connection to the database **/
/*********************************************************************************************************/
public function __destruct() {
if($this->con) {
$close = $this->con->close();
if(!$close)
trigger_error("Unable to close database connection in Mysqli::close", E_USER_WARNING);
}
}
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "db_interaction" is the only function (beside the con-/destructor) interacting directly **/
/** with the scripts. It works out which function is needed by requesting script, running through it, **/
/** getting data out of it and sending this data to the requesting script. **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS: **/
/** req $action string - name of action, which function should be called (ex. select, insert) **/
/** req $args array - array of parameters, varies from function to function **/
/** opt $multi bool - triggers dimension of return array (true: $arr[$key][$field][$val] / **/
/** false: $arr[$field][$val] **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $result array - contains return-data of called function (or false) **/
/*********************************************************************************************************/
public function db_interaction($action, $args, $multi = true) {
$result = false;
if($this->con) {
$this->fetch_multi = $multi;
$call_function = "db_".$action;
$result = $this->$call_function($args);
}else{
trigger_error("No existing object Mysql::con in Mysql::db_interaction()", E_USER_ERROR);
}
return $result;
}
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "db_count" counts how many records in a defined table fit to a certain condition **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS: **/
/** req $args[0] string - column name **/
/** req $args[1] string - table name **/
/** opt $args[2] string - condition with wildcards (ex. WHERE ID = ?) **/
/** opt $args[3] array - parameters, which replace the wildcards in condition **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $count int - amount of records matching to the condition or false if query failed **/
/*********************************************************************************************************/
private function db_count($args) {
$count = 0;
if(count($args) == 2) {
$sql = "SELECT COUNT(".$args[0].") AS '".$args[0]."' FROM `".$args[1]."`";
$result = $this->con->query($sql);
if(!$result) {
trigger_error("Error in function Mysql::db_count: ".$this->con->error, E_USER_ERROR);
}else{
$row = $result->fetch_row();
$count = $row[0];
$result->close();
}
}elseif(count($args) == 4) {
$sql = "SELECT COUNT(".$args[0].") AS '".$args[0]."' FROM `".$args[1]."` ".$args[2];
$result = $this->con->prepare($sql);
if(!$result) {
trigger_error("Error in function Mysql::db_count: ".$this->con->error, E_USER_ERROR);
}else{
$this->bind_stmt_param($result, $args[3]);
$row = $this->fetch_data($result);
$count = $row[0][$args[0]];
$result->close();
}
}else{
trigger_error("Wrong number of parameters in function Mysql::db_count", E_USER_ERROR);
}
return $count;
}
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "db_delete" deletes one or more records in a defined table accordings to a condition **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS: **/
/** req $args[0] string - table name **/
/** req $args[1] string - condition with wildcards (ex. WHERE ID = ?) **/
/** req $args[2] array - parameters, which replace the wildcards in condition **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $success bool - contains true / false, whether the delete statement succeed or failed **/
/*********************************************************************************************************/
private function db_delete($args) {
$success = false;
if(count($args) == 3) {
$sql = "DELETE FROM `".$args[0]."`".$args[1];
$result = $this->con->prepare($sql);
if(!$result) {
trigger_error("Error in function Mysql::db_delete: ".$this->con->error, E_USER_ERROR);
}else{
$this->bind_stmt_param($result, $args[2]);
$success = $result->execute();
$result->close();
}
}else{
trigger_error("Wrong number of parameters in function Mysql::db_delete", E_USER_ERROR);
}
return $success;
}
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "db_insert" inserts a record in a defined table **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS: **/
/** req $args[0] string - table name **/
/** req $args[1] array - hash built up the following way ($hash['ColumnName'] = "Value") **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $success bool - contains true / false, whether the insert succeeded or failed **/
/*********************************************************************************************************/
private function db_insert($args) {
$success = false;
if(count($args) == 2) {
$values = "";
$cols = implode(", ", array_keys($args[1]));
//prepare wildcards: as many columns are affected, as many wildcards (?) are printed in a string
for($i=1;$i<=count($args[1]);$i++) {
$values .= "?";
if($i != count($args[1])) $values .= ", ";
}
$sql = "INSERT INTO ".$args[0]." (".$cols.") VALUES (".$values.")";
$result = $this->con->prepare($sql);
if(!$result) {
trigger_error("Error in function Mysql::db_insert: ".$this->con->error, E_USER_ERROR);
}else{
$this->bind_stmt_param($result, $args[1]);
$success = $result->execute();
$result->close();
}
}else{
trigger_error("Wrong number of parameters in function Mysql::db_insert", E_USER_ERROR);
}
return $success;
}
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "db_select" selects records from a defined table **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS: **/
/** req $args[0] string - columns to read out **/
/** req $args[1] string - table, where the columns are read out from **/
/** opt $args[2] string - condition with wildcards (ex. WHERE ID = ?) OR "ORDER BY" **/
/** opt $args[3] array - array with parameters, which replaces the wildcards in condition **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $ret_hash array - contains return-data of called function (or false) **/
/*********************************************************************************************************/
private function db_select($args) {
$ret_hash = false;
$sql_type = count($args);
switch($sql_type) {
// eg. "SELECT * FROM `table`"
case 2: $sql = "SELECT ".$args[0]." FROM `".$args[1]."`";
$result = $this->con->query($sql);
break;
// eg. "SELECT * FROM `table` ORDER BY ID DESC"
case 3: $sql = "SELECT ".$args[0]." FROM `".$args[1]."` ".$args[2];
$result = $this->con->query($sql);
break;
// eg. "SELECT Row FROM `table` WHERE ID = 1"
case 4: $sql = "SELECT ".$args[0]." FROM `".$args[1]."` ".$args[2];
$result = $this->con->prepare($sql);
break;
default: trigger_error("Wrong number of parameters in function Mysql::db_select", E_USER_ERROR);
}
if(!$result) {
trigger_error("Error in function Mysql::db_select: ".$this->con->error, E_USER_ERROR);
}else{
if($sql_type == 4) {
$this->bind_stmt_param($result, $args[3]);
}
$ret_hash = $this->fetch_data($result);
$result->close();
}
return $ret_hash;
}
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "db_update" alters data in records from a defined table **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS (depend on whether plain query or prepared statement has to be used): **/
/** req $args[0] string - table, where data has to be altered **/
/** PLAIN QUERY: **/
/** req $args[1] string - rest of query-string (eg. counter = counter + 1 WHERE ID = value) **/
/** PREPARED STATEMENT: **/
/** req $args[1] array - hash built up the following way ($hash['ColumnName'] = "Value") **/
/** req $args[2] string - condition with wildcards (ex. WHERE ID = ?) **/
/** req $args[3] array - array with parameters, which replaces the wildcards in condition **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $success bool - contains true / false, whether the update succeeded or failed **/
/*********************************************************************************************************/
private function db_update($args) {
$success = false;
$sql_type = count($args);
if($sql_type == 2) {
$sql = "UPDATE ".$args[0]." SET ".$args[1];
$result = $this->con->query($sql);
if(!$result)
trigger_error("Error in function Mysql::db_update ($sql_type = 2): ".$this->con->error, E_USER_ERROR);
else
$success = true;
}elseif($sql_type == 4) {
/*************************************************************************************************/
/** The aim of the following lines is, to alter $args[1] which is $hash['ColumnName'] = "Value" **/
/** Afterwards there results an array $cols['ColumnName'] = "?" **/
/** AND **/
/** every "Value" is writen in the array $u_arr to handle with the function "bind_param" **/
/*************************************************************************************************/
$values = array_reverse(array_values($args[1]));
if(is_scalar($args[3])) $u_args[0] = $args[3];
else $u_args = $args[3];
foreach($values as $k => $v)
array_unshift($u_args, $v);
foreach($args[1] as $k => $v)
$cols[$k] = "?";
$set_string = implode(", ", array_map(array($this, "cb_prep_update_hash"), array_keys($cols), array_values($cols)));
$sql = "UPDATE ".$args[0]." SET ".$set_string." ".$args[2];
$result = $this->con->prepare($sql);
if(!$result) {
trigger_error("Error in function Mysql::db_update: ".$this->con->error, E_USER_ERROR);
}else{
$this->bind_stmt_param($result, $u_args);
$success = $result->execute();
$result->close();
}
}else{
trigger_error("Wrong number of parameters in function Mysql::db_update", E_USER_ERROR);
}
return $success;
}
/*********************************************************************************************************/
/** OTHER FUNCTIONS **/
/*********************************************************************************************************/
/*********************************************************************************************************/
/** DESCRIPTION: **/
/** Function "fetch_data" gets data out of a resource and writes it either in a normal or a smarty **/
/** readable multidimensional array. Done that the second way, data can be accessed the follwowing way: **/
/** $array[iterator]['ColumnName']. **/
/** --------------------------------------------------------------------------------------------------- **/
/** PARAMETERS: **/
/** req $res resource - normaly an instance of mysqli_stmt or mysqli_result (prep stmt || query) **/
/** --------------------------------------------------------------------------------------------------- **/
/** RETURNS: **/
/** $return_arr array - array with the prep stmt or query result **/
/*********************************************************************************************************/
private function fetch_data($res) {
$return_arr = [];
if($res instanceof mysqli_stmt) {
$res->execute();
$res->store_result();
// get result set metadata from prepared statement
$meta = $res->result_metadata();
// create an array of field names
while($field = $meta->fetch_field())
$field_arr[] = $field->name;
$param_arr = $this->bind_result_param($res, $field_arr);
$i = 0;
while($res->fetch()) {
for($o=0;$o<count($field_arr);$o++) {
if($this->fetch_multi) {
$return_arr[$i][$field_arr[$o]] = $param_arr[$o];
}else{
$return_arr[$field_arr[$o]] = $param_arr[$o];
}
}
$i++;
}
}elseif($res instanceof mysqli_result) {
while($row = $res->fetch_assoc()) {
$return_arr[] = $row;
}
}else{
trigger_error("Param $res is no valid resource in Mysql::fetch_data", E_USER_ERROR);
}
return $return_arr;
}
/*********************************************************************************************************/
/** Function "bind_stmt_param" binds a set of parameters out of an array to a prepared statement. This **/
/** function works with the following parameters: **/
/** required resource - resource to a mysqli_stmt **/
/** required array - array of parameters to send to the function "bind_param" **/
/*********************************************************************************************************/
private function bind_stmt_param($res, $plain_args) {
if($res instanceof mysqli_stmt) {
$param_arr = $this->get_param_arr($plain_args);
$ref_arr = array();
for($i=0;$i<count($param_arr);$i++) {
$ref_arr[$i] = &$param_arr[$i];
}
call_user_func_array(array($res, "bind_param"), $ref_arr);
}else{
trigger_error("Param $res is no valid resource in Mysql::bind_stmt_param", E_USER_ERROR);
}
}
/*********************************************************************************************************/
/** Function "bind_result_param" binds data of a result sets to specified variables. This function **/
/** works with the following parameters: **/
/** req $res resource - resource to a mysqli_stmt **/
/** req $field_arr array - array of fieldnames to send to the function "bind_result" **/
/*********************************************************************************************************/
private function bind_result_param($res, $field_arr) {
if($res instanceof mysqli_stmt) {
// bind_result() expects parameters to be references. so $field_arr is referenced in $ref_arr to feed bind_result() correctly
for($i=0;$i<count($field_arr);$i++)
$ref_arr[$i] = &$field_arr[$i];
// future column output is bound to "column names"
call_user_func_array(array($res, "bind_result"), $ref_arr);
return $ref_arr;
}else{
trigger_error("Param $res is no valid resource in Mysql::bind_result_param", E_USER_ERROR);
}
}
/*********************************************************************************************************/
/** Function "get_param_arr" handles an array of parameters and brings them in a form, which works with **/
/** the function MySQLi_STMT::bind_param(). This function works with the following parameters: **/
/** req $args array - array of parameters, which should be bound to a prepared statement **/
/** Returns: array(string $type_string, mixed $var_1, mixed $var_2, ..., mixed $var_n) **/
/*********************************************************************************************************/
private function get_param_arr($args) {
$type_string = "";
if(is_scalar($args))
$f_args[0] = $args;
else
$f_args = $args;
foreach($f_args as $key => $val) {
if(is_bool($val)) $val = intval($val);
if(is_float($val)) $type_string .= "d";
elseif(is_int($val)) $type_string .= "i";
elseif(is_string($val)) $type_string .= "s";
$param_arr[] = $val;
}
array_unshift($param_arr, $type_string);
return $param_arr;
}
/*********************************************************************************************************/
/** CALLBACK - FUNCTIONS **/
/*********************************************************************************************************/
/** Function "cb_prep_update_hash" merges the keys of the update hash with the values of the same hash **/
/** and creates a proper syntax to update a table. **/
/*********************************************************************************************************/
private function cb_prep_update_hash($col, $val) {
$update_statement = $col." = ".$val;
return $update_statement;
}
/*********************************************************************************************************/
}
?>Edit: mysqli::execute_query() macht wohl Dreiviertel des Codes überflüssig.
Zuletzt bearbeitet:
