mysql_db_table

A class that wraps MySQLi Db and manages tables for quick inserts / updates

Releases:

• 1.10 May-11-2009

public function insert($data=null)

Insert a new row into a table.

// Execute an INSERT via variable setting
$table = new mysqli_db_table('users');
$table->username = 'billy';
$table->password = '123456';
$table->insert();
 
// Execute an INSERT via array setting
$_POST['username'] = 'billy';
$_POST['password'] = '123456';
 
$table = new mysqli_db_table('users');
$table->insert($_POST);
 
// Execute an INSERT with SQL Functions
$table->insert(array(
    'username'=>'billy',
    'register_date'=>SQL('NOW()')
));

• data Mixed array

mysqli_db_table class

public function update($data=null, $id=null)

Creates a connection or uses the exsiting connection.

// Execute an update via array setting
$_POST['id']       = '1';
$_POST['username'] = 'billy';
$_POST['password'] = '123456';
 
// Update By Primary 
$table = new mysqli_db_table('users');
$table->update($_POST, $_POST['id']);
 
// Update With Custom Where
$table = new mysqli_db_table('users');
$id = $table->filter($_POST['id]);
$table
    ->where('id = ' . $id)
    ->update($_POST);

• data Mixed Array.
• id Table's Primary Key.

mysqli_db_table class

connect

public function get($id = null)

Fetch a single row based on a primary key. The key is filtered.

// Get User By Primary Key 
$table = new mysqli_db_table('users');
$user = $table->get(23);

• id Table's Primary Key.

mysqli_db_table class

fetch_all fetch_one

public function delete($id = null)

Deletes a Row.

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

• id Primary Key to delete.

public function where($where)

Add WHERE selections to fetch_one, fetch_all(), fetch_singlet, delete, update.

// Execute a DELETE via where
$table = new mysqli_db_table('users');
$table->where('user_id = ' . $table->filter($_POST['id']));
$table->delete();
 
// Execute a DELETE via multiple wheres (AND's are used to merge)
$table = new mysqli_db_table('users');
$table->where('user_id = ' . $table->filter($_POST['id']));
$table->where('register_date > NOW()');
$table->delete();
//-- OUTPUT: 'DELETE FROM `user` WHERE user_id=1 AND register_date > NOW()'

• where String

mysqli_db_table class

select

public function select($select)

Adds SELECT selections to fetch_one, fetch_all(), fetch_singlet.

// Execute a SELECT via where & select
$table = new mysqli_db_table('users');
$table->select('firstname, lastname');
$table->where('user_id = ' . $table->filter($_POST['id']));
$table->fetchOne();
 
// Execute a SELECT via where & multiple selects
$table = new mysqli_db_table('users');
$table->select('firstname')->select('lastname');
$table->where('user_id = ' . $table->filter($_POST['id']));
$table->fetchOne();

• where String

mysqli_db_table class

where

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

Returns all rows in a query.

// Execute a query
$db->query('SELECT * FROM blog_entries');
$entries = $db->fetch_all();

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

Grabs the first column in the first row of a dataset.

$table = new mysqli_db_table('users');
$table->select('username, password');
$table->where('user_id = ' . $table->filter($_POST['id']));
$table->fetchSinglet();
 
//-- OUTPUT: 'billy'

• 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