I am trying to create a PHP / MySQLi wrapper class that uses prepared statements, the goal of the class is to create a connection to the Database, then INSERT / SELECT / UPDATE information from the database.
The class should hopefully be general enough to be able to use in multiple applications with other wrapper classes that call on this class with more specific query functions.
Please let me know what you think, and any way I could improve this class.
<?php
class Db {
// The database connection
protected static $connection;
/**
* Connect to the database
*
* @return bool false on failure / mysqli MySQLi object instance on success
*/
function __construct() {
$this->connect();
}
public function connect() {
// Try and connect to the database
if(!isset(self::$connection)) {
// get information from config file to connect to database
self::$connection = new mysqli('localhost', DB_USER, DB_PASS,DB_NAME);
}
// If connection was not successful, handle the error
if(self::$connection === false) {
// Handle error - notify administrator, log to a file, show an error screen, etc.
return false;
}else{
//echo message for testing purpose
echo ' connection successful' ;
}
return self::$connection;
}
/**
* Create a secure hash
*
* Creates a secure copy of the user password for storage
* in the database.
*
* @param string $password The user's created password
* @param string $nonce A user-specific NONCE
* @return string $secureHash The hashed password
*/
//TODO UPDATE TO USE PHP.DEFAULT HASH FUNCTIONS
function hash_password($password, $nonce) {
$secureHash = hash_hmac('sha512', $password . $nonce, SITE_KEY);
return $secureHash;
}
/**
* Query the database
*
* @param $query The query string
* @return mixed The result of the mysqli::query() function
*/
public function query($query) {
// Connect to the database
$connection = $this -> connect();
// Query the database
$result = $connection -> query($query);
return $result;
}
/**
* Fetch rows from the database (SELECT query)
*
* @param $query The query string
* @return bool False on failure / array Database rows on success
*/
public function select($query) {
$rows = array();
$result = $this -> query($query);
if($result === false) {
return false;
}
while ($row = $result -> fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
/**
Insert function using prepared statements
**/
public function insert($table,$data,$format){
if(empty( $table )|| empty ( $data ) ) {
return false;
}
//Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format ;
//build format array
$format = implode('' ,$format);
$format = str_replace('%','',$format);
list($fields, $placeholders, $values ) = $this->prep_query($data) ;
// prepends format onto values
array_unshift($values ,$format) ;
//prepare our query for binding
$stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders}]) ");
call_user_func_array( array( $stmt . 'bind_param'), $this->ref_values($values)) ;
$stmt->execute() ;
if($stmt->affected_rows){
return true ;
}
return false ;
}
/**
* Prepared Update statement
*
*
*
*
*
*
*/
public function update($table,$data,$format,$where,$where_format){
if ( empty ($table) || empty( $data)){
return false ;
}
$data = (array) $data ;
$format = (array) $format ;
//build format array
$format = implode('', $format);
$format = str_replace('%','', $format) ;
$where_format = implode('', $where_format) ;
$where_format = str_replace('%' ,'',$where_format) ;
$format .- $where_format ;
list($fields,$placeholders,$values) = $this->prep_query($data, 'update') ;
$where_clause = '' ;
$where_values = '' ;
$count = 0 ;
foreach($where as $fields => $value){
if($count > 0 ){
$where_clause .= ' AND ' ;
}
$where_clause .=$field .'=?';
$where_values[] = $value ;
$count++ ;
}
array_unshift($values,$format);
$values = array_merge($values,$where_values) ;
$stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}") ;
call_user_func_array(array( $stmt , 'bind_param'), $this -> ref_values($values)) ;
$stmt->execute();
if($stmt->affected_rows){
return true ;
}
return false ;
}
function get($sql) {
$resource = $db->query($sql);
//Loop through to grab all rows
while($row = $db->fetch_assoc( $resource )){
$results[] = $row;
}
//array_pop($results);
return $results;
}
function ref_values($array){
$refs = array() ;
foreach($array as $key=>$value){
$refs[$key] = &$array[$key];
}
return $refs ;
}
function prep_query($data,$type='insert') {
$fields = '' ;
$placeholders = '' ;
$values = array() ;
foreach($data as $field => $value) {
$fields .= "{$field},";
$values[] = $vales;
if($type == 'update'){
$placeholders .= $field. '=?, ' ;
}else{
$placeholders .= '?, ' ;
}
}
}
}
?>
1 Answer 1
Formatting
The elephant in the room: the formatting is really, really bad. Haphazard spaces, indents, and inexplicable things like this:
/**
* Prepared Update statement
*
*
*
*
*
*
*/
Surely you can do better if you try.
Keep in mind that code is read far more often than it is written. Favoring write-time convenience over read-time convenience is a false economy.
Connection
The connect method is called every time you execute a query, and it checks every time if a connection object exists and creates if necessary. Why not do that in the constructor, only once, And let the rest of the code simply reuse the connection object directly?
Simplify
Instead of this:
if($stmt->affected_rows){
return true ;
}
return false ;
How about simply:
return $stmt->affected_rows
Pointless comments
Many of the comments are unnecessary. Take for example:
//echo message for testing purpose
echo ' connection successful' ;
That echo statement seems kinda self-explanatory ain't it...
queryfunction and force the user to make use of prepared statement. \$\endgroup\$