Feb
18
2013
Classes and functions // MySQL // PHP

Custom PHP MySQLi Database Class

Author An article by admin    Comments 15 Comments

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', '[email protected]' ); //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!

[dm]8[/dm]

Tags: , , , , ,

Related Posts

15 Comments + Add Comment

  • ajib August 6, 2013 at 5:10 pm

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

    Reply
    • Bennett Stone September 13, 2013 at 3:36 am

      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

      Reply
  • Edward August 14, 2013 at 6:51 pm

    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

    Reply
    • Bennett Stone October 11, 2013 at 5:38 am

      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 :))

      Reply
  • 2TJ August 31, 2013 at 9:02 pm

    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;
    }

    Reply
  • 2TJ August 31, 2013 at 9:06 pm

    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) );

    Reply
  • Andy October 14, 2013 at 3:27 am

    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

    Reply
  • Pawanvir Singh May 5, 2014 at 6:31 am

    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

    Reply
  • anagnam June 3, 2014 at 5:16 pm

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

    Reply
  • Per Thomsen December 12, 2014 at 9:49 am

    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)

    Reply
    • Bennett Stone February 4, 2015 at 4:52 am

      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

      Reply
  • Edgar Estrada May 27, 2017 at 12:20 am

    Hello!
    How about calling stored procedures using this class? I cracked my head for 2 days to solve that issue using another DB class, and now it’s broken so I will try yours.

    Thanks!
    -E

    Reply

Leave a comment