mysql_db

A class that wraps MySQLi and manages Mysql database connections and queries

Releases:

• 1.20.2 April-22-2010

Initializes the object.

$db = new mysqli_db(); // No connection executed
 
// Constructor Connection
$db = new mysqli_db('localhost', 'master', 'pass', 'clients'); // Create connection at construction

Creates a connection or uses the exsiting connection.

// Using Constants
define('DB_SERVER', 'localhost');
define('DB_USER', 'master');
define('DB_PASSWD', 'pass');
define('DB_NAME', 'clients');
$db = mysqli_db::init();
 
// Or using parameter
$db = mysqli_db::init('localhost', 'master', 'pass', 'clients');

• server MySQL Server's location.
• user The MySQL username.
• passwd The MySQL password.
• db The MySQL database name.

mysqli_db class

connect

Creates a connection. Just like init but always creates a new connection

// Non-Constructor connection
$db = new mysqli_db();
$db->connect('localhost', 'master', 'pass', 'clients');
 
// Constructor Connection
$db = new mysqli_db('localhost', 'master', 'pass', 'clients');

• server MySQL Server's location.
• user The MySQL username.
• passwd The MySQL password.
• db The MySQL database name.

public function fetch_all($query = null, $params = null, $cached = false)

Returns all rows in a query. If you are using the cached parameter then please make sure your table is typed as “MyISAM”.

// Execute a query
$db->query('SELECT * FROM blog_entries');
$entries = $db->fetch_all();
 
// Simple Cache Example
$db->query('SELECT * FROM blog_entries', null, 'blog_entires');
$entries = $db->fetch_all();
 
// Complex Cache Example
$tickets = $db->fetch_all('
    SELECT b.*, o.fake_id, 
        (b.cost_total - b.cost_tax) * (b.concierge_commission * 0.01) AS commision_total
    FROM bookedtickets AS b 
    LEFT JOIN orders AS o ON (o.bookedticket_id = b.bookedticket_id)
    WHERE b.concierge_id = ?', 
    array($_SESSION['concierge_id']),
    array('bookedtickets', 'orders') //-- Check both tables for last updates
);

• query The statement
• params (optional) The unprepared values for the statement

array of associated rows

fetch_one, fetch_singlet, query

public function fetch_one($query = null, $params = null)

Returns one row in a query.

// Execute a query
$db->query('SELECT * FROM blog_entries');
$entry = $db->fetch_one();
 
// Fetch all rows with fetch_one
$results = array();
for($i=0; $i < $db->count(); $i++) {
    $results[] = $db->fetchOne();
}
 
// --OR--
 
// Fetch all rows with fetch_one
$results = array();
while($row = $db->fetch_one()) {
    $results[] = $row;
}

• query The statement
• params (optional) The unprepared values for the statement

array of an associated row

fetch_all, fetch_singlet, query

public function fetch_singlet($query = null, $params = null)

Returns first associated column in a SELECT query.

// Execute a query
$db->query('SELECT id FROM blog_entries');
$entryid = $db->fetchSinglet();
// returns 1

• query The statement
• params (optional) The unprepared values for the statement

array of an associated row

fetch_all, fetch_one, query

public function count()

The count of rows in a query modified or gathered.

TODO: Example

interger

last_id

public function last_id()

The last inserted id. The id must be a primary auto-incremented id.

// Execute a query via Prepared statement
$entry_id    = 23;
$entry_title = 'Testy test';
$db->query('INSERT INTO blog_entries (id, title) VALUES(?,?)', array($entry_id, $entry_title));
echo $db->lastid();
// returns: 23

interger

count

public function debug()

The last MySQL query ran.

// Execute a query via Prepared statement
$entry_id    = 23;
$entry_title = 'Testy test';
$db->query('SELECT * FROM blog_entries WHERE id=? AND title=?', array($entry_id, $entry_title));
echo $db->debug();
// returns: SELECT * FROM blog_entries WHERE id=23 AND title="Testy test"

string

error

public function error()

The error of the last query ran.

// Execute a query via Prepared statement
$entry_id    = 23;
$entry_title = 'Testy test';
$db->query('SELECT * FROM blog_entries WHERE id=? AND title=?', array($entry_id, $entry_title));
echo $db->debug();
// returns: SELECT * FROM blog_entries WHERE id=23 AND title="Testy test"

string

debug

public function filter($args)

Filter an array or string of variables from mysql injections, adds quotes if a string.

$vars = $db->filter(array('cat'=>'i haz cheezburger'));
$db->query('INSERT INTO blog_entries (title) VALUES(' . $vars['cat'] . ')');
echo $db->debug;
// returns: INSERT INTO blog_entries (title) VALUES("i haz cheezburger")

array, string, interger, decimal

public function prepare($stmt)

The last inserted id. The id must be a primary auto-incremented id.

// Execute a query
$db->prepare('SELECT * FROM blog_entries WHERE id=?')->bind(23)->query();
$entry = $db->fetch_one();
// or
$db->prepare('SELECT * FROM blog_entries WHERE id=?');
$db->bind(23);
$db->query();
$entry = $db->fetch_one();
//or
$db->query('SELECT * FROM blog_entries WHERE id=?', array(23));
$entry = $db->fetch_one();

mysqli_db (self)

bind

public function bind($params)

Sets up an aray of variables or a single variable to use alongisde a prepared statement.

// Execute a query
$db->prepare('SELECT * FROM blog_entries WHERE id=?')->bind(23)->query();
// or
$db->prepare(SELECT * FROM blog_entries WHERE id=?);
$db->bind(23);
$db->query();
// you can also stack binds
$db->prepare('SELECT * FROM blog_entries WHERE id=? and title=?')
    ->bind(23)->bind('Hello World')->query();
$db->prepare('SELECT * FROM blog_entries WHERE id=? and title=?')
    ->bind(array(23,'Hello World'))->query();
$entry = $db->fetch_one();
 
// you can do can use them all in the same parameter
$db->prepare('SELECT * FROM events WHERE name LIKE ? OR name LIKE ? and location LIKE ?')
    ->bind('%My%', '%Event%', '%San%')
    ->query();
 
// you can do can use an array and if they have keys you can assign you ?'s to them
$db->prepare('SELECT * FROM events WHERE name LIKE ?arg1 OR name LIKE ?arg2 and location LIKE ?')
    ->bind(array('arg2'=>'%Event%'), '%San%', array('arg1'=>'%My%'))
    ->query();
 
// a cleaner example notice they can be in any order but you probably shouldn't
$db->prepare('SELECT * FROM events WHERE name LIKE ?arg1 OR name LIKE ?arg2 and location LIKE ?')
    ->bind(array('arg2'=>'%Event%', '%San%', 'arg1'=>'%My%'))
    ->query();
 
// and yes keys can be used multiple times
$db->prepare('SELECT * FROM events WHERE name LIKE ?arg1 OR name LIKE ?arg1 and location LIKE ?')
    ->bind(array('%San%', 'arg1'=>'%My%'))
    ->query();

mysqli_db (self)

prepare

public function query($query = null, $params = null)

Execute a MySQL command.

// Execute a query
$db->query('SELECT * FROM blog_entries');
$entry = $db->fetch_one();
 
// Execute a query via Prepared statement
$db->query('SELECT * FROM blog_entries WHERE id = ?', array($entry_id));
$entry = $db->fetch_one();
 
// Execute a query via Non-Escaped SQL
$db->query('SELECT * FROM blog_entries WHERE pub_date >= ?', array(SQL('NOW()')));
// or prepare/bind
$db
    ->prepare('SELECT * FROM blog_entries WHERE pub_date >= ?')
    ->bind(SQL('NOW()'));
$entry = $db->fetch_one();

integer (last inserted id, row count, rows modified)

fetch_one, fetch_all, fetch_singlet