This is a wrapper for a PHP MySQL class, which utilizes MySQLi. This is inspired from CodeIgniter php framework. Most of the functions and documentations are identical to CodeIgniter database class and user guide and are extracted from it.

You do not need to use CodeIgniter to use this wrapper. Simply include the class file and you are good to go.

PHP 5.3 + MySQLi is required.

• Welcome

  <ul>
  	<li><a href="#markdown-header-usage">Usage</a></li>
  	<li><a href="#markdown-header-select-query">SELECT Query</a></li>
  	<li><a href="#markdown-header-manual-db-query">Manual : $db-&gt;query()</a></li>
  	<li><a href="#markdown-header-get-only-the-first-row-db-query_first">Get only the first row : $db-&gt;query_first()</a></li>
  	<li><a href="#markdown-header-executing-query-db-execute">Executing Query : $db-&gt;execute()</a></li>
  	<li><a href="#markdown-header-using-active-record-pattern">Using Active Record Pattern</a></li>
  	<li><a href="#markdown-header-select-query-db-select-from">SELECT Query : $db-&gt;select()-&gt;from()</a></li>
  	<li><a href="#markdown-header-distinct-db-distinct">DISTINCT : $db-&gt;distinct();</a></li>
  	<li><a href="#markdown-header-from-clause-db-from">FROM clause : $db-&gt;from()</a></li>
  	<li><a href="#markdown-header-where-clause-db-where">WHERE clause : $db-&gt;where()</a></li>
  	<li><a href="#markdown-header-or_where-clause-db-or_where">OR_WHERE clause : $db-&gt;or_where()</a></li>
  	<li><a href="#markdown-header-where-in-db-where_in">WHERE IN: $db-&gt;where_in()</a></li>
  	<li><a href="#markdown-header-or-where-in-db-or_where_in">OR WHERE IN: $db-&gt;or_where_in()</a></li>
  	<li><a href="#markdown-header-where-not-in-db-where_not_in">WHERE NOT IN : $db-&gt;where_not_in()</a></li>
  	<li><a href="#markdown-header-or-where-not-in-db-or_where_not_in">OR WHERE NOT IN: $db-&gt;or_where_not_in()</a></li>
  	<li><a href="#markdown-header-parenthesis-between-where">Parenthesis between WHERE</a></li>
  	<li><a href="#markdown-header-select-from-where-execute">SELECT + FROM + WHERE + Execute</a></li>
  	<li><a href="#markdown-header-fetching-the-result-db-fetch">Fetching the result : $db-&gt;fetch()</a></li>
  	<li><a href="#markdown-header-fetching-the-first-row-db-fetch_first-or-db-fetch_result">Fetching the first row : $db-&gt;fetch_first() or $db-&gt;fetch_result()</a></li>
  	<li><a href="#markdown-header-limit-and-offset-db-limit">LIMIT and OFFSET : $db-&gt;limit()</a></li>
  	<li><a href="#markdown-header-get-db-get-from-version-151">GET : $db-&gt;get() ( from version 1.5.1)</a></li>
  	<li><a href="#markdown-header-select_max-db-select_max">SELECT_MAX : $db-&gt;select_max()</a></li>
  	<li><a href="#markdown-header-select_min-db-select_min">SELECT_MIN : $db-&gt;select_min()</a></li>
  	<li><a href="#markdown-header-select_avg-db-select_avg">SELECT_AVG : $db-&gt;select_avg()</a></li>
  	<li><a href="#markdown-header-select_sum-db-select_sum">SELECT_SUM : $db-&gt;select_sum()</a></li>
  	<li><a href="#markdown-header-inserting-data-db-insert">Inserting Data : $db-&gt;insert()</a></li>
  	<li><a href="#markdown-header-update-query-db-update">Update query : $db-&gt;update()</a></li>
  	<li><a href="#markdown-header-last-query-db-last_query">Last Query : $db-&gt;last_query()</a></li>
  	<li><a href="#markdown-header-dry-run-db-dryrun">Dry Run : $db-&gt;dryrun()</a></li>
  	<li><a href="#markdown-header-escape-string-db-escape">Escape String : $db-&gt;escape()</a></li>
  	<li><a href="#markdown-header-like-db-like">LIKE : $db-&gt;like()</a></li>
  	<li><a href="#markdown-header-or-like-db-or_like">OR LIKE : $db-&gt;or_like()</a></li>
  	<li><a href="#markdown-header-having-db-having">HAVING : $db-&gt;having()</a></li>
  	<li><a href="#markdown-header-or-having-db-or_having">OR HAVING : $db-&gt;or_having()</a></li>
  	<li><a href="#markdown-header-group-by-db-group_by">GROUP BY : $db-&gt;group_by()</a></li>
  	<li><a href="#markdown-header-order-by-db-order_by">ORDER BY : $db-&gt;order_by()</a></li>
  	<li><a href="#markdown-header-delete-db-delete">DELETE : $db-&gt;delete();</a></li>
  	<li><a href="#markdown-header-dry-run-on-delete-query">Dry Run on DELETE query</a></li>
  	<li><a href="#markdown-header-join-db-join">JOIN : $db-&gt;join();</a></li>
  	<li><a href="#markdown-header-find_in_set-db-find_in_set-from-version-143">FIND_IN_SET : $db-&gt;find_in_set() ( from version 1.4.3)</a></li>
  	<li><a href="#markdown-header-between-db-between-from-version-146">BETWEEN : $db-&gt;between() ( from version 1.4.6)</a></li>
  </ul>
  </li>
  

To use this class, import it to your project

<?php

require_once 'class.database.php' ;

Once the class file is included, initialize it

<?php

$db = new Database($host, $username, $password, $database);

If your MySQL installation is using a non standard port, you can specify the port as

<?php

$db = new Database($host, $username, $password, $database, $port);

If you are going to use a table prefix, you can assign it as

<?php

$db->set_table_prefix('wp_'); // Sets wp_ as table prefix

A query string can be generated in two ways

1. Manual query
2. Using active records

<?php

$sql = "SELECT * FROM table" ;
$db->query($sql) ;

query_first() can be used to get the first row of the query.

<?php

$db->query_first("SELECT * FROM table") ;
// Produces: SELECT * FROM table LIMIT 1 ;

<?php
$sql = "SELECT * FROM table" ;
$db->query($sql) ;
$db->execute(); 

// Also using method chaining

$sql = "SELECT * FROM table";
$db->query($sql)->execute() ;

echo "Affected Rows : " . $db->affected_rows ;  // Outputs the affected rows

The following function permits you to write the SELECT portion of your query

<?php

$db->select('title, content, date');
// Produces: SELECT title, content, date

$db->select('*');
// Produces: SELECT *

If you do not call the select() method, "SELECT *" will be assumed. If no parameter is given, select() will assume *

<?php

$db->distinct();

// Produces: SELECT DISTINCT

<?php

$db->from('table') ; // Set the table name

You can also chain the methods such as

<?php

$db->select("id, email")->from('table') ;
// Produces : SELECT id, email FROM table

The general syntax for where() is

<?php 

$db->where('a', 'b' ); 
$db->where('c', 'd' ); 

You can also feed an array as

<?php 

$db->where( array
          ('a' => 'b',
           'c' => 'd'
) ;

<?php

$where = array(
     'name' => 'test',
     'email' => '[email protected]'
);

$db->where($where);
// Produces: WHERE name = 'test' AND email = 'email' 
// Using method chaining

$db->select()->from('table')->where($where) ;
// Produces: SELECT * FROM table WHERE name = 'test' AND email = 'email' 

// You can also skip select() if you want. 
$db->from('table')->where($where) ;
// Produces: SELECT * FROM table WHERE name = 'test' AND email = 'email' 

<?php
$db->where('name !=', $name);
$db->or_where('id >', $id);

// Produces: WHERE name != 'Joe' OR id > 50

Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate

<?php
$names = array('Frank', 'Todd', 'James');
$db->where_in('username', $names);
// Produces: WHERE username IN ('Frank', 'Todd', 'James')

Generates a WHERE field IN ('item', 'item') SQL query joined with OR if appropriate

<?php
$names = array('Frank', 'Todd', 'James');
$db->or_where_in('username', $names);
// Produces: OR username IN ('Frank', 'Todd', 'James')

Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate

<?php
$names = array('Frank', 'Todd', 'James');
$db->where_not_in('username', $names);
// Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')

Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR if appropriate

<?php
$names = array('Frank', 'Todd', 'James');
$db->or_where_not_in('username', $names);
// Produces: OR username NOT IN ('Frank', 'Todd', 'James')

To open a parenthesis, use open_where() and to close use close_where()

<?php
$db->select('column')->from('table');
$db->where('foo', 15);
$db->open_where();
$db->or_where('foo <', 15);
$db->where('bar >=', 15);
$db->close_where();

// Produces  SELECT `column` FROM `table` WHERE `foo` = 15 OR (`foo` < 15 AND `bar` >= 15) 

The following example combines all the function to get the result easily

<?php

$db->select()->from('table')->where($where)->execute(); 
$affected = $db->affected_rows ; // Gets the total number of rows selected 

// Again, you can skip the select() method if you are selecting all fields (*)
$db->from('table')->where($where)->execute();

The result will be output as associative array when the fetch() is called. You do not need to call execute() before you call fetch(). The functions execute() and fetch() acts like same. The former does not return data and the latter returns an array with the data. In both the cases, $db->affected_rows will be set.

<?php


$rows = $db->from('table')->where($where)->fetch(); 
echo $db->affected_rows ; // Output the total number of selected rows 

foreach($rows as $row )
{
   var_dump($row);
}

// Or in short

$rows = $db->from('table')->fetch();
var_dump($rows);
// Produces: SELECT * FROM table

This function will return only the first row of the result

<?php
$array = $db->from('table')->fetch_first() ;

// Produces SELECT * FROM table LIMIT 1
// Returns an array

<?php 

$db->limit(1); 

// Produces the limit part :  LIMIT 1

$db->limit(1,2);

// Produces the limit and offset : LIMIT 1,2 

// Example

$db->select()->from('table')->limit(1,5)->execute();

// Produces: SELECT * FROM table LIMIT 1, 5

Get function saves time by calling multiple functions at once.

$db->select('*')->from('table')->fetch();

The same code can be expressed using

$db->get('table');

Get also takes Limit as the second parameter and Offset as the third parameter. These parameters are optional.

$db->select('*')->from('table')->limit(1,2)->fetch();

is equivalent to

$db->get('table', 1, 2);

Writes a "SELECT MAX(field)" portion for your query. You can optionally include a second parameter to rename the resulting field.

<?php
$result = $db->select_max('age')->from('table')->fetch();

// Produces: SELECT MAX(age) AS age FROM members

$result = $db->select_max('age', 'member_age')->from('table')->fetch();;

// Produces: SELECT MAX(age) AS member_age FROM members

Writes a "SELECT MIN(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

<?php
$result = $db->select_min('age')->from('table')->fetch();

// Produces: SELECT MIN(age) AS age FROM members

$result = $db->select_min('age', 'member_age')->from('table')->fetch();;

// Produces: SELECT MIN(age) AS member_age FROM members

Writes a "SELECT AVG(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

<?php
$result = $db->select_avg('age')->from('table')->fetch();

// Produces: SELECT AVG(age) AS age FROM members

$result = $db->select_avg('age', 'member_age')->from('table')->fetch();;

// Produces: SELECT AVG(age) AS member_age FROM members

Writes a "SELECT SUM(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

<?php
$result = $db->select_sum('age')->from('table')->fetch();

// Produces: SELECT SUM(age) AS age FROM members

$result = $db->select_sum('age', 'member_age')->from('table')->fetch();;

// Produces: SELECT SUM(age) AS member_age FROM members

<?php

$data = array(
    'title' => 'Some title',
    'email' => '[email protected]', 
    'created' => 'NOW()'

);

$id = $db->insert('tableName', $data) ; // $id will have the auto-increment 

echo "Data inserted. ID:" . $id ;

update() method can be used to update a table with the data. Update() expects that you already set the WHERE clause and LIMIT before calling update().

<?php

$where = array(
         'email' = > '[email protected]',
         'id' => 14
);

$data = array(
         'email' => '[email protected]',
         'password' => 'pass1'
);

$db->where($where)->update('table', $data); 

// Produces: UPDATE table SET email = '[email protected]', password = 'pass1' WHERE email = '[email protected]' AND id = '14' ;

// NOTE: where() should be called BEFORE update(), otherwise it will be ignored. 

// You can also use or_where()

$db->or_where($where)->update('table', $data); 
// Produces: UPDATE table SET email = '[email protected]', password = 'pass1' WHERE email = '[email protected]' OR id = '14' ;

This function will return the last generated query string. Useful for debugging purpose.

<?php

$this->select('id')->from('table')->where('name', 'test')->execute();
echo $db->last_query();

// Produces: SELECT id FROM table where name = 'test' ;

Dry run will output the query string which is ready to be executed. If you call dryrun() then the query will not be executed. And the last_query() will output the query which is ready to be executed.

This function is often useful in case of calling DELETE or UPDATE function. The developer can view the DELETE or UPDATE query generated, before executing it.

<?php
$data['email'] = '[email protected]';

echo $db->dryrun()->update('table', $data)->last_query();

// Produces: UPDATE table SET email = '[email protected]' , and the query is NOT executed. 

This function returns sanitized data for mysql operation

<?php

$string = "where 's a and 's" ;
echo $db->escape($string);

// Produces: where \'s a and \'s

This function enables you to generate LIKE clauses, useful for doing searches.

Simple key/value method:

<?php

$db->like('title', 'match');

// Produces: WHERE title LIKE '%match%' 

If you use multiple function calls they will be chained together with AND between them:

<?php

$db->like('title', 'match');
$db->like('body', 'match');

// WHERE title LIKE '%match%' AND body LIKE '%match%

If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default).

<?php

$db->like('title', 'match', 'before');
// Produces: WHERE title LIKE '%match'

$db->like('title', 'match', 'after');
// Produces: WHERE title LIKE 'match%'

$db->like('title', 'match', 'both');
// Produces: WHERE title LIKE '%match%' 

If you do not want to use the wildcard (%) you can pass to the optional third argument the option 'none'.

<?php

$db->like('title', 'match', 'none');
// Produces: WHERE title LIKE 'match' 

Associative array method

<?php

$array = array('title' => $match, 'page1' => $match, 'page2' => $match);

$db->like($array);

// WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%'

This function is identical to the one above, except that multiple instances are joined by OR:

<?php
$db->like('title', 'match');
$db->or_like('body', $match);

// WHERE title LIKE '%match%' OR body LIKE '%match%'

Permits you to write the HAVING portion of your query. There are 2 possible syntaxes, 1 argument or 2:

<?php
$db->having('user_id = 45');
// Produces: HAVING user_id = 45

$db->having('user_id', 45);
// Produces: HAVING user_id = 45

You can also pass an array of multiple values as well:

<?php
$db->having(array('title =' => 'My Title', 'id <' => $id));

// Produces: HAVING title = 'My Title', id < 45

Identical to having(), only separates multiple clauses with "OR".

Permits you to write the GROUP BY portion of your query:

<?php
$db->group_by("title");

// Produces: GROUP BY title 

You can also pass an array of multiple values as well:

<?php
$db->group_by(array("title", "date"));

// Produces: GROUP BY title, date

Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are asc or desc ( case insensitive)

<?php
$db->order_by("title", "desc");

// Produces: ORDER BY title DESC 

You can also pass your own string in the first parameter:

<?php
$db->order_by('title desc, name asc');

// Produces: ORDER BY title desc, name asc

Or multiple function calls can be made if you need multiple fields.

<?php
$db->order_by("title", "desc");
$db->order_by("name", "asc");

// Produces: ORDER BY title DESC, name ASC 

In addition to this, you can also use an array for multiple calls

<?php
$order_by = array('title' => 'desc',
                  'name'  => 'asc'
);

$db->order_by($order_by);

// Produces: ORDER BY title DESC, name ASC 

Permits you to write DELETE statement. Delete function takes one optional argument - table. Also note that delete() requires that you call execute() at the end to execute the query

<?php

$db->delete('table')->execute();

// Produces: DELETE FROM table

If table is not provided, it will take the table name set by '$db->from()' method

<?php
$db->->delete()->from('table')->execute();

// Produces: DELETE FROM table

You can also use where(), limit(), having(), like() etc with delete method

<?php

$db->delete()->from('table')->where('email', '[email protected]')->limit(1)->execute();

// Produces: DELETE FROM table WHERE email = '[email protected]' LIMIT 1

delete() will also gives you the total number of rows deleted.

<?php

$db->delete()->from('table')->execute();
echo "Deleted Rows: " . $db->affected_rows ;

Delete is a dangerous query which will irreversibly delete your data from the table. If you are not sure how the delete query will be generated, you can run dryrun() and it will give you the generated query which is ready to be executed.

<?php

$db->dryrun()->delete('table')->exexute()->last_query();
// Outputs DELETE FROM table , but does not execute the query 

Permits you to write JOIN statement.

<?php
$db->select('*');
$db->from('blogs');
$db->join('comments', 'comments.id = blogs.id');


// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.id = blogs.id

Multiple function calls can be made if you need several joins in one query. If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

<?php
$db->join('comments', 'comments.id = blogs.id', 'left');

// Produces: LEFT JOIN comments ON comments.id = blogs.id

Generates a FIND_IN_SET query. Takes 3 parameters. First parameter is the string to find. Second parameter is the column name to search, and third parameter which is optional, will take an operator AND or OR to join multiple WHERE clauses.

<?php
$db->find_in_set('503', 'orders')->from('tblinvoices')->fetch();
// Produces: SELECT *  FROM tblinvoices  WHERE  FIND_IN_SET ('305', orders) 

$db->where('id', 5)->find_in_set('503', 'orders')->from('tblinvoices')->fetch();
// Produces: SELECT *  FROM tblinvoices WHERE id='5' AND  FIND_IN_SET ('305', orders) 

$db->where('id', 5)->find_in_set('503', 'orders', 'OR')->from('tblinvoices')->fetch();
// Produces: SELECT *  FROM tblinvoices WHERE id='5' OR  FIND_IN_SET ('305', orders) 

Generates a BETWEEN condition.

<?php
$db->between('created', '2014-05-05', '2014-05-10');

// Produces: created BETWEEN '2014-05-05' AND '2014-05-10'

$db->from('tblinvoices')->where('clientid', '12')->between('created', '2014-05-05' , '2014-05-10')->fetch();

// Produces: SELECT * FROM tblinvoices WHERE clientid = '12' AND created BETWEEN '2014-05-05' AND '2014-05-10'