Feb
18
2013

Custom PHP MySQLi Database Class

Updated class files (documentation remains the same) with better error handling and full OOP class structure can be found here.

Since the depreciation of the mysql functions in PHP (archive here), I thought it may be handy to more effectively future-proof my web applications by wrapping my database interactivity functions in a class (and oh how it is!), and as I was building a new web framework anyway- I figured why not!

Unrelated: featured image is a good example of why I’m better off programming than designing (winky face)

The problem with much of my prior code, and many of my inherited codebases is the lack of uniformity when dealing with database interactivity.  For example, to perform a standard query utilizing a while-loop typically looks like…

$query = mysql_query("SELECT * FROM your_table") or die(mysql_error());
while( $row = mysql_fetch_array( $query ) )
{
    //Do stuff
}

However, in the event that you don’t want to display mysql errors to visitors, you suddenly have a problem.  In any given file, there are multiple iterations and versions of any number of database queries, all of which must be modified, and possibly (though hopefully not) re-modified in the event that what you’ve done isn’t providing the desired outcome.

That being the case, the simplest solution is to use functions to handle queries more effectively, thereby making any changes only necessary in a single location that will affect queries globally.

$database = new DB();
$query = "SELECT group_name FROM your_table";
$results = $database->get_results( $query );
foreach( $results as $row )
{
    echo $row['group_name'];
}

Using the above example, if for some reason my “get_results” function should suddenly handle queries differently, or more effectively, I can open a single file, make the change, and get on with my life!

Enter class.db.php

Normally I’m not particularly attached to classes since I personally prefer light codebases and rarely (if at all possible) have code for sites that is used in a single location, however, since not all pages of a website necessarily interact with the database (and I know how much people love classes!), I’ve created the class.db.php– a straightforward php mysqli wrapper to handle all of your database interaction needs (and mine)!

Of note, I am still actively developing this class, however, it is currently being used and tested on production websites, and is stable.  Additionally, this class does not automatically sanitize user data, you must still sanitize user data using $database->filter( $data );

Usage

Just to be clear, you have to include the database class to use it, after which the class must be initiated:

require_once( 'class.db.php' );
$database = new DB();

After the class has been included and initiated, you simply call the appropriate function and away you go!

It currently handles:

  • filter (sanitize user data)
  • get_results (get all associated results and return as array)
  • get_row (self explanatory)
  • num_rows (return an int with the number of results for the given query)
  • insert
  • update
  • delete
  • lastid (get the last ID from an auto increment value)
  • exists (determine if a value exists, returns a boolean)
  • table_exists
  • truncate (single table, or array of tables)
  • list_fields
  • num_fields
  • db_common (added function to allow for simplified usage with mysqli functions such as AES_ENCRYPT and now())

Getting started

The download at the bottom of the post contains 3 files:

  • The class.db.php file
  • example.php which demonstrates the capabilities and usage of the class
  • example-data.sql which contains a table create statement as well as some sample data referenced in example.php

Be sure to set your host, user, and configuration details at the top of the example.php file:

define( 'DB_HOST', 'localhost' ); // set database host
define( 'DB_USER', 'root' ); // set database user
define( 'DB_PASS', 'root' ); // set database password
define( 'DB_NAME', 'yourdatabasename' ); // set database name
define( 'SEND_ERRORS_TO', 'you@yourwebsite.com' ); //set email notification email address
define( 'DISPLAY_DEBUG', true ); //display db errors?

Data sanitizing

The simplest way to sanitize data in the case of multiple inputs is to loop through $_POST or $_GET values, filter, and reassemble:

foreach( $_POST as $key => $value )
{
    $_POST[$key] = $database->filter( $value );
}

It isn’t that the class isn’t capable of sanitizing all data without an explicit call to the filter() function, however, due to some interesting data submission types (ajax filtered => php filtered specifically) causing extraneous slashes and multi-html-encoded entities, I have removed auto filtering at the time of this writing.

Retrieving data from the database

Note: all of the following examples assume the database class has already been included, and the class has been initiated with:

$database = new DB();
$query = "SELECT group_name, group_id FROM your_table";
$results = $database->get_results( $query );
foreach( $results as $row )
{
    echo $row['group_name'] . '
';
    echo $row['group_id'];
}

Retrieving a single row of data

$query = "SELECT group_id, group_name, group_parent FROM your_table WHERE group_name LIKE '%production%'";
list( $id, $name, $parent ) = $database->get_row( $query );
//Will return: 15, Production Tools, 8

Determining the number of results from a query

$number = $database->num_rows( "SELECT group_name FROM your_table" );
echo $number;

Inserting data into the database

Takes 2 parameters: table name, and an array of field => values

//The fields and values to insert
$names = array(
    'group_parent' => 18,
    'group_name' => 'Awesomeness'
);
$add_query = $database->insert( 'your_table', $names );

Update database rows

Takes 4 parameters:

  1. table name
  2. array of field => values to update
  3. array of field => equals values for the “where” clause.
  4. int limit rows to update
//Fields and values to update
$update = array(
    'name' => 'Awesome',
    'city' => 'Brooklyn'
);

//Add the WHERE clauses
$where_clause = array(
    'name' => 'Bennett Stone',
    'id' => 3
);
$updated = $database->update( 'your_table', $update, $where_clause, 1 );

//Output errors if they exist for the update query
$database->display( $updated );

Deleting rows from the database

Takes 3 parameters:

  1. Table name
  2. The “where” clause as “column => value” array
  3. Number of rows to limit (optional)
//Run a query to delete rows from table where id = 3 and name = Awesome, LIMIT 1
$delete = array(
    'id' => 3,
    'name' => 'Awesome'
);
$deleted = $database->delete( 'your_table', $delete, 1 );

Checking to see if a value exists

Returns a boolean value

$checksum = array( 'group_name' => 'bennett' );
$column_to_check = 'group_id';
$exists = $database->exists( $column_to_check, 'your_table',  $checksum );
if( $exists )
{
    echo 'Bennett DOES exist!';
}

Getting the last insert ID

$last_id = $database->lastid();

Checking to see if a table exists

if( !$database->table_exists( 'your_table' ) )
{
    //Run a table install, the table doesn't exist
}

Truncating tables

Variables are always passed as an array, echo query to display the number of tables truncated

//Truncate a single table, no output display
$truncate = $database->truncate( array('your_table') );

//Truncate multiple tables, display number of tables truncated
echo $database->truncate( array('your_table', 'my_other_table') );

List the fields in a table

echo $database->num_fields( 'SELECT * FROM your_table' );

Display the number of fields in a table

echo $database->num_fields( 'SELECT * FROM your_table' );

Get it!

http://www.phpdevtips.com/wp-content/plugins/downloads-manager/img/icons/default.gif download: Custom MySQLi DB Class (7.27KB)

14 Comments + Add Comment

  • I like this class and this article too. I only missing a method to binding parameters (prepare) as
    2TJ wrote. It will make the class superior to others.

    Cheers
    Per :o)

    • You’re totally correct, I’ve been meaning to issue an update as I recently made a PDO variant of the same which is quite portable (since I didn’t personally want to rewrite the code I’d written for many projects using the non-pdo version), available here: https://github.com/bennettstone/SimplePDO

  • can you also do a PDO version of this? (which they say is much more secure. dunno what’s your thoughts about it)

  • Hi,

    Could you please guide me how can access further functions values like if I want to know whether my query affected any row or not.

    Regards,
    Pawanvir Singh

  • public function __construct()
    {
    global $connection;
    mb_internal_encoding( ‘UTF-8’ );
    mb_regex_encoding( ‘UTF-8’ );

    $this->link = new mysqli( DB_HOST, DB_USER, DB_PASS, DB_NAME );
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    $this->link->set_charset(“utf8”);
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    if( mysqli_connect_errno() )
    {
    $this->log_db_errors( “Connect failed: %s\n”, mysqli_connect_error(), ‘Fatal’ );
    exit();
    }
    }

    —–
    I used the class, but I found,
    The class.db.php has to add ‘$this->link->set_charset(“utf8”);’,
    otherwise, the results will incorrect,
    Otherwise, the

  • Another nice to have:
    Some method to exec query binding parameters as array, something like…
    db->method( “select * from table where param1 = ? and param2 = ?”, array(value1, value2) );

  • I am testing your class now, good work!
    You should add some static singleton, for example…

    protected static $_instance;

    function __construct(…) {
    ….
    self::$_instance = $this;
    }

    public static function getInstance() {
    return self::$_instance;
    }

  • Nice article, although I have a question. Your insert, delete, update functions seems to be limited to only queries involving criterias where equals(=) are involved. How would you go about dealing with clauses that involve greater than(>), less than(<) or any others?

    Thanks,
    Edward

    • For those particular queries, you could use the $db->query( “SELECT name FROM user_names WHERE name LIKE ‘%edward%'” ) format, or I could add a $db->like() function (which I will probably do anyway :))

  • how to call stored procedure using this mysqli class v2?
    before this i using v1 everything running OK

    • You should still be able to use the $database->query() to call a stored procedure, nothing on that side was modified, just updates to make it fully OOP

Leave a comment