5
\$\begingroup\$

I wrote this class recently to better structure and handle MySQLi related interactions with PHP and would love to get some feedback.

<?php
/*
 * simpleMysqli
 * 
 * @usage This class is meant to be extended, with
 * the resulting class setting the $table parameter
 * in it's __construct().
 * 
 */
class simpleMysqli extends mysqli
{
    private $database = array(
        'host' => '',
        'username' => '',
        'password' => '',
        'database' => ''
    );
    public $table = '';

    /*
     * __construct($databaseConfig = array())
     * 
     * Instantiate simpleMysqli class with optional database config.
     * 
     * @param (databaseConfig) An optional database configuration
     * containing host, username, password, database.
     * 
     */ 
    public function __construct($databaseConfig = array())
    {
        $this->database = ($databaseConfig) ? $databaseConfig : $this->database;

        parent::__construct();
        $this->real_connect(
            $this->database['host'], 
            $this->database['username'],
            $this->database['password'],
            $this->database['database']
        );
    }

    /*
     * preparedQuery
     * 
     * Structures, maps and executes a prepared MySQLi query.
     * 
     * @param (mappedQuery) A properly formatted SimpleMYSQLi query.
     * @param (mappedParameters) An associative array of parameters
     * to map into the mappedQuery.
     * @param (returnInsertId) Returns the insert id for insert queries.
     * @param (returnAffectedRows) Returns the number of rows affected by
     * the executed query.
     * 
     * @return A MySQLi result, or insert id, or affected rows depending
     * on what parameter values were passed. 
     */
    public function preparedQuery($mappedQuery, $mappedParameters = array(), $returnInsertId = 0, $returnAffectedRows = 0)
    {
        $dynamicBindingParameters = array();

        $types = '';
        foreach ($mappedParameters as $mapping => $value) {
            // replace first occurence only
            $position = strpos($mappedQuery, $mapping);
            if ($position !== false) {
                $mappedQuery = substr_replace($mappedQuery, '?', $position, strlen($mapping));
            }
            $dynamicBindingParameters[] = &$mappedParameters[$mapping];
            $types .= (is_int($value)) ? 'i' : 's';

            $mappedParameters[$mapping] = (is_null($value)) ? '' : $value;
        }
        $dynamicBindingParameters = array_merge(array(&$types), $dynamicBindingParameters);

        $statement = $this->prepare($mappedQuery);
        if ($statement == false) {
            trigger_error('Bad SQL: ' . $mappedQuery . ' Error: ' . $this->errno . ' ' . $this->error);
        }

        // dynamically bind parameters.
        call_user_func_array(array($statement, 'bind_param'), $dynamicBindingParameters);

        $statement->execute();

        $return = $statement->get_result();
        $return = ($returnInsertId) ? (string)$this->insert_id : $return;
        $return = ($returnAffectedRows) ? $statement->affected_rows : $return;

        return $return;
    }

    /*
     * genericQuery
     * 
     * Executes a non-prepared statement query against
     * the specified table. NOTE: Do not trust passing user
     * submitted data into the query parameter as 
     * MySQL injection will then be a possibility.
     * 
     * @param (query) A non-prepared statement query.
     * @param (returnInsertId) Returns 
     * 
     * @return A query result or insert id depending
     * on what parameter values were passed.
     * 
     */
    public function genericQuery($query, $returnInsertId = 0)
    {
        $result = $this->query($query);
        return ($returnInsertId) ? (string)$this->insert_id : $result;
    }


    /*
     * insert
     * 
     * Executes an insert query using an associative
     * array of data.
     * 
     * @param (data) An associative array containing
     * the data to be inserted. Array keys represent
     * the columns to insert into, the corresponding
     * values of those keys are the values that will
     * be inserted into the table.
     * 
     * @return Returns an insert id.
     * 
     */
    public function insert($data = array())
    {
        $mappedQuery = "INSERT INTO $this->table";
        $mappedQueryColumnString = '(';
        $mappedQueryValueString = 'VALUES(';

        $mappedParameters = array();

        $i = 0;
        foreach ($data as $column => $value) {
            $mappedQueryColumnString .= ($i > 0) ? ", $column" : $column; 
            $mappedQueryValueString .= ($i > 0) ? ", :$column" : ":$column";
            $mappedParameters[":$column"] = ($value) ? $value : '';

            $i++;
        }
        $mappedQueryColumnString .= ')';
        $mappedQueryValueString .= ')';

        $mappedQuery .= ' ' . $mappedQueryColumnString . ' ' . $mappedQueryValueString;

        return $this->preparedQuery($mappedQuery, $mappedParameters, 1);
    }

    /*
     * update
     * 
     * Executes an update query using an associative
     * array of data.
     * 
     * @param (data) An associative array containing
     * the data to be inserted. Array keys represent
     * the columns to insert into, the corresponding
     * values of those keys are the values that will
     * be inserted into the table.
     * 
     * @return Returns non 0 value on success.
     * 
     */ 
    public function update($checkColumn, $checkValue, $data = array())
    {
        $mappedQuery = "UPDATE $this->table SET";
        $mappedQueryConditionString = "WHERE $checkColumn = :$checkColumn";
        $mappedQueryUpdateString = '';

        $mappedParameters = array();

        $i = 0;
        foreach ($data as $column => $value) {
            $mappedQueryUpdateString .= ($i > 0) ? ", $column = :$column" : "$column = :$column";
            $mappedParameters[":$column"] = ($value) ? $value : '';

            $i++;
        }
        $mappedParameters[":$checkColumn"] = $checkValue;

        $mappedQuery .= ' ' . $mappedQueryUpdateString . ' ' . $mappedQueryConditionString;
        return $this->preparedQuery($mappedQuery, $mappedParameters, 1);
    }

    /*
     * get
     * 
     * @param (checkColumn) The column of the table to
     * match the checkValue against.
     * @param (checkValue) The value to match.
     * 
     * @return Returns a row from the table that matches
     * a specified checkValue for a specified checkColumn.
     * 
     */
    public function get($checkColumn, $checkValue)
    {
        $mappedQuery = "SELECT * FROM $this->table WHERE $checkColumn = :$checkColumn LIMIT 1";
        $mappedParameters = array(
            ":$checkColumn" => $checkValue
        );

        return $this->getSingle($mappedQuery, $mappedParameters);
    }

    /*
     * getSingle
     * 
     * @param (query) A mappable select query.
     * @param (mappedParameters) The parameters to
     * map into the query.
     * 
     * @return Returns a single row from the table matching
     * the mapped select query.
     */ 
    public function getSingle($query, $mappedParameters = array())
    {
        $queryResult = ($mappedParameters) ? $this->preparedQuery($query, $mappedParameters) : 
                                             $this->genericQuery($query);
        return $queryResult->fetch_assoc();
    }

    /*
     * getMulti
     * 
     * @param (query) A mappable select query.
     * @param (mappedParameters) The parameters to
     * map into the query.
     * 
     * @return Returns multiple rows from the table matching
     * the mapped select query.
     * 
     */ 
    public function getMulti($query, $mappedParameters = array())
    {
        $queryResult = ($mappedParameters) ? $this->preparedQuery($query, $mappedParameters) :
                                             $this->genericQuery($query);
        $queryResult->data_seek(0);

        $results = array();
        while($row = $queryResult->fetch_assoc()) {
            $results[] = $row;
        }

        return $results;
    }

    /*
     * getRows
     * 
     * @param (query) A mappable select query.
     * @param (mappedParameters) The parameters to
     * map into the query
     * 
     * @return Returns the number of rows matched by
     * the mapped select query.
     * 
     */
    public function getRows($query, $mappedParameters = array())
    {
        $queryResult = ($mappedParameters) ? $this->preparedQuery($query, $mappedParameters) : 
                                             $this->genericQuery($query);

        return $queryResult->num_rows;
    }   
}

Usage Examples

/*
 * The following is a basic class extending 
 * the SimplyMysql class showing it's usage.
 * 
 * For the usage pattern, a new php class
 * is used for each table in the database 
 * we're working with. This allows for better
 * organization of our queries.
 * 
 */
class usersTableSimpleMysqliExample extends simpleMysqli
{
    public function __construct()
    {
        /*
         * You don't need to pass a database configuration 
         * in the following construct if you specify it
         * for the database parameter in the simpleMysqli
         * class we're extending.
         * 
         * Regardless, you will always need to call parent::__construct()
         * in order to properly setup the database connection.
         * 
         */
        parent::__construct(array(
            'host' => '',
            'username' => '',
            'password' => '',
            'database' => ''
        ));

        /*
         * The table within our specified database we'll
         * accessing and manipulating from this specific class.
         */
        $this->table = 'users';
    }

    /*
     * return a single row for our query.
     */
    public function someCustomQuery($userId, $name)
    {
        $this->getSingle(
            "SELECT *
             FROM users
             WHERE id = :userId
             AND name = :name",
            array(
                ':userId' => $userId,
                ':name' => $name
            )
        );
    }

    /*
     * return multiple rows for our custom query
     */
    public function getAllNewUsers()
    {
        $this->getMulti(
            "SELECT *
             FROM users
             WHERE created > UNIX_TIMESTAMP() - 86400"      
        );
    }

    /*
     * returns multiple rows for out customer query
     */
    public function getAllNewGenderUsers($gender)
    {
        $this->getMulti(
            "SELECT *
             FROM users
             WHERE gender = :gender",
            array(
                ':gender' => $gender
            )       
        );
    }
}
/*
 * 
 * Example usage.
 * 
 */
class exampleUsage
{
    public static function someExample()
    {
        $usersTableSimpleMysqliExample = new usersTableSimpleMysqliExample();

        // insert example
        $usersTableSimpleMysqliExample->insert(array(
            'firstName' => 'Braydon',
            'lastName' => 'Batungbacal',
            'someData' => json_encode(array('hello', 'hello'))
        ));

        // update example
        $usersTableSimpleMysqliExample->update('userId', 24, array(
            'firstName' => 'Some New Name',
            'lastName' => 'foo',
            'someOtherColumn' => 'bar'
        ));

        // delete example
        $usersTableSimpleMysqliExample->preparedQuery(
            "DELETE
             FROM users
             WHERE id = :userId", 
            array(
                ':userId' => 24 
            )
        );
    }
}

GitHub

\$\endgroup\$
2
\$\begingroup\$

After watching your class, some remarks I would like to do:

preparedQuery

In preparedQuery method, the declaration asks for two different ways of returning the data. I think it would be better just have one variable to manage all of them and check it with a switch:

Instead of:

public function preparedQuery($mappedQuery, $mappedParameters = array(), $returnInsertId = 0, $returnAffectedRows = 0)
{
    $dynamicBindingParameters = array();

    // [...]

    $return = $statement->get_result();
    $return = ($returnInsertId) ? (string)$this->insert_id : $return;
    $return = ($returnAffectedRows) ? $statement->affected_rows : $return;

    return $return;
}

This:

class simpleMysqli extends mysqli
{
    const RETURN_RESULT = 1;
    const RETURN_ID = 2;
    const RETURN_AFFECTED_ROWS = 3;


public function preparedQuery($mappedQuery, $mappedParameters = array(), $return = simpleMysqli::RETURN_RESULT)
{
    $dynamicBindingParameters = array();

    // [...]

    switch ($return) {                        
        case RETURN_RESULT:
        default:
            $statement->get_result();
            break;
        case RETURN_ID:
            $this->insert_id
            break;
        case RETURN_AFFECTED_ROWS:
            $statement->affected_rows
            break;
    }

    return $return;
}

This way is tidy, and easy to modify. You can use default to set an Exception if wished, but it's up to you depending on how strict you want to do if they don't provide the right string. Using constants helps you with having the name of the return in the code, what is better for readability.

Insert and Update

In your 'insert' and 'update' method, you use this piece of code to concatenate all the values in a string:

$mappedQueryColumnString = '(';
$mappedQueryValueString = 'VALUES(';

$mappedParameters = array();

$i = 0;
foreach ($data as $column => $value) {
    $mappedQueryColumnString .= ($i > 0) ? ", $column" : $column; 
    $mappedQueryValueString .= ($i > 0) ? ", :$column" : ":$column";
    $mappedParameters[":$column"] = ($value) ? $value : '';

    $i++;
}
$mappedQueryColumnString .= ')';
$mappedQueryValueString .= ')';

Well, that does the trick, but I think is better and easy using array and not mess with commas and creating that:

$mappedQueryColumns = array();
$mappedQueryValues = array();

foreach ($data as $column => $value) {
    $mappedQueryColumns[]  = $column; 
    $mappedQueryValues[]   = ":$column";
    $mappedParameters[":$column"] = ($value) ? $value : '';
}

// And here, you concatenate all without trouble:
$mappedQueryColumnString = '(' . implode(', ', $mappedQueryColumnValues) . ')';
$mappedQueryValueString =  'VALUES(' . implode(', ', $mappedQueryColumnValues) . ')';

That way you forget about comma, checks, etc...

get

I will give the user the option to select the fields their wishes, because a 'SELECT *' can be a bullet on the stomach to the performance if the table is big or with a lot of fields... I would allow an array of fields to be listed, and * by default (better that than nothing). Something like:

public function get($checkColumn, $checkValue, $fields = '*')
{
    if (is_array($fields)) {
        $fieldString = implode(', ', $fields);                      
    } else {
        $fieldString = '*';
    }

    $mappedQuery = "SELECT $fieldString FROM $this->table WHERE $checkColumn = :$checkColumn LIMIT 1";

And lately, I think you should try to merge getSingle and getMulti, they pretty much do the same and the lines are repeated. Both could be using a private method with they feed with different params, deppending if they want a single or a multiple result.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ @BraydonBatungbacal if you like the answer, perhaps you could throw this guy an upvote too! \$\endgroup\$ – janos Jun 28 '15 at 19:12

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.