4
\$\begingroup\$

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 .= '?, ' ;
            }
        }
    }


}



?>
\$\endgroup\$
  • \$\begingroup\$ Your class is very hard to read through because some of the indents are mis-aligned, most IDE's have code formatting - it'll make it a lot easier for you (and everyone else) to read \$\endgroup\$ – CᴴᵁᴮᴮʸNᴵᴺᴶᴬ Jul 6 '15 at 13:51
  • \$\begingroup\$ When coding, I'd recommend following a style guide such as php-fig.org/psr/psr-2 \$\endgroup\$ – Anthony G - justice for Monica Jul 6 '15 at 14:06
  • \$\begingroup\$ It looks like you allow to send any kind of query to the database without a validation. As you develop a wrapper class I'd disallow calling query function and force the user to make use of prepared statement. \$\endgroup\$ – AMartinNo1 Jul 6 '15 at 14:39
2
\$\begingroup\$

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...

\$\endgroup\$

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.