Mysqli-DB-Class

Manipulate with your data using simple methods

Powerful actions in simple Active Record style. No any SQL writing. Fast and low-memory usage, compact and cleaner resulting code.

Upload sdba folder on your server.
Open db_config.php and configure connection with your database.

public static $dbname = 'dbname'; // Your database name
public static $dbuser = 'dbuser'; // Your database username
public static $dbpass = 'dbpass'; // // Your database password
public static $dbhost = 'localhost'; // Your database host, 'localhost' is default.
public static $dbencoding = 'utf8'; // Your database encoding, default is 'utf8'. Do not change, if not sure.

include sdba.php in your script

First request

Create object of your table, for example - table users. Lets retrieve all rows from users and write in $users_list variable.

$users = DB::table('users'); // creating table object
$users_list = $users->get(); // retrieving all rows from users table

Features

The script allows you to organize the work with database with a simple methods and without writing queries manually.

Simple insert, update and delete
Batch inserts and updates
Where conditions and fulltext search
Sorting and grouping
Joins of multiple tables
Foreign keys updating
Checking the uniqueness of records
Concatenation and group concatenation
Min, max, sum and total
Increments and decrements
You do not need to remember about tildes and quotes

System Requirements and Limitations

System Requirements

PHP5
MySQL5
mysqli extention

get

Get rows from table as list of associative arrays.

array get ( [int $limit] [, int $start] )

Will be loaded only limit rows, stating from start. If both parameters in not set, will be loaded all found rows.

$articles = Sdba::table(``'articles'``);

$list = $articles``->get(10) // loads 10 articles

get_one

Get one row from table as associative array.

array get ( void )

Method has no parameters

$articles = Sdba::table(``'articles'``);

$item = $articles``->get_one() // loads 1 article

get_list

Get rows from table as list values.

array get ( mixed $field [, int $limit] [, int $start] [, string $table] )

field - selected field, can be single field(strind), or array(key=>value) and will be placed in key=>value list. Will be loaded only limit rows, stating from start. If both parameters in not set, will be loaded all found rows; table - sets a table, that's different from current instance.

$articles = Sdba::table(``'articles'``);

$list = $articles``->get(10) // loads 10 articles

get_single

Get one row from table as associative array.

string get ( string $field [, string $table] )

field - selected field; table - sets a table, that's different from current instance.

$articles = Sdba::table(``'articles'``);

$item = $articles``->get_one() // loads 1 article

total

Returns count of rows in a table.

int total ( void )

Method has no parameters

$articles = Sdba::table(``'articles'``);

$count = $articles``->total(); // returns number of rows in table

delete

Deletes rows from table. You can define specific rows using where() method.

void delete( [int $limit] [, int $start] [, string $table] )

limit - maximum rows for deleting, start - start number of row, table - sets a table, that's different from current instance

$articles = Sdba::table(``'articles'``);

$articles``->where(``'id'``, 55);

$articles``->``delete``();

insert

Inserts row(s) in database.

void insert( [array $arr] [, string $table] )

arr - array of rows values or list of arrays, table - sets a table, that's different from current instance

$users = Sdba::table(``'users'``);

$data = array``(``'name'``=>``'John'``,``'email'``=>``'john@example.com'``);

$users``->insert(``$data``);

// or batch insert

$data = array``(

array``(``'name'``=>``'John'``,``'email'``=>``'john@example.com'``);

array``(``'name'``=>``'Bob'``,``'email'``=>``'bob@example.com'``);

array``(``'name'``=>``'Kate'``,``'email'``=>``'kate@example.com'``);

);

$users``->insert(``$data``);

insert_id

Get insert id of last inserted item

int insert_id( void )

$new_user = array``(``'email'``=>``'user@example.com'``, 'password'``=>``'123456'``);

$users = Sdba::table(``'users'``);

$users``->insert(``$new_user``);

$user_id = $users``->insert_id();

update

Updates row(s) in database. You can define specific rows using where() method.

void update( array $arr [, int $limit] [, int $start] [, string $table] )

arr - array of rows values or list of arrays, limit - maximum rows for updating, start - start number of row, table - sets a table, that's different from current instance

$users = Sdba::table(``'articles'``);

$users``->where(``'created <'``, '2012-10-30'``);

$data = array``(``'published'``=> 0);

$users``->update(``$data``);

update_fk

Rebuilds relations for foreign key.

void update_fk(string $left_key_name, string $left_key_val, string $right_key_name, mixed $right_key_val [, string $table] )

left_key_name - name of foreign key, left_key_val - value of foreign key, right_key_name - name of relation key, right_key_val - new relation values (can be comma-separated string or array), table - sets a table, when it is different from current instance

$fk_articles_categories = Sdba::table(``'articles_fk'``);

$fk_articles_categories``->update_fk(``'art_id'``,15,``'cat_id'``,``'2,3,8,12'``);

//or

$keys = array``(``'2'``,``'3'``,``'8'``,``'12'``);

$fk_articles_categories``->update_fk(``'art_id'``,15,``'cat_id'``,``$keys``);

set

Inserts row(s) in database or updates when row already exist.

void set( array $arr )

arr - list of arrays, duplicate rows will be updated

$users = Sdba::table(``'users'``);

$data = array``(

array``(``'id'``=>5,``'name'``=>``'John'``,``'email'``=>``'john@example.com'``); //will be updated, if 'id' found

array``(``'name'``=>``'Bob'``,``'email'``=>``'bob@example.com'``); // will be inserted

array``(``'name'``=>``'Kate'``,``'email'``=>``'kate@example.com'``); // will be inserted

);

$users``->set(``$data``);

fields

Sets field, that will bee loaded from database, or not will be loaded when reverse is true.

object fields( mixed $fields [, bool $reverse] [, string $table] )

fields - fields from table (can be comma-separated string or array), reverse - reversing method action, table - sets a table, when it is different from current instance

$users = Sdba::table(``'users'``);

$users``->fields(``'name'``,``'email'``); // will extract only name and email columns

$user_list = $users``->get();

alias

Sets alias for field in result set

object alias(mixed $field, string $alias [, string $table] )

fields - result field, can be string or field=>alias array; alias - new name of field in result set; table - table, when it is different from current instance.

$users = Sdba::table(``'users'``);

$users``->alias(``'id'``,``'user_id'``);

$users``->get();

where

Sets where condition. Affects the methods get, get_one, total, update, delete.

object where( mixed $field [, string $value] [, string $table] [, bool $no_quotes] [, string $join_with] [, string $operator] )

field - table field (can be string with operator, or field=>value array), value - condition value, table - table, when it is different from current instance, no_quotes - prevent value from inserting quotes (need for use mysql functions in where, like NOW() etc), join_with - operator for joining condition, operator - condition operator, system feature.

$users = Sdba::table(``'users'``);

$users``->where(``'active'``, 1);

$user_list = $users``->get();

and_where

Alias of where(), used for convenience and readability.

object and_where( string $field [, string $value] [, string $table] [, bool $no_quotes] )

$users = Sdba::table(``'users'``);

$users``->where(``'active'``, 1)->and_where(``'sex !='``,``'man'``);

$user_list = $users``->get();

or_where

Alias of where(), used for convenience and readability.

object or_where( string $field [, string $value] [, string $table] [, bool $no_quotes] )

$users = Sdba::table(``'users'``);

$users``->where(``'active'``, 0)->or_where(``'banned'``,1);

$user_list = $users``->get();

like

Creates like condition, based on where().

object like( string $field, string $value [, string $table] [, array $pattern] [, string $join_with] )

field - table field (can be string with operator, or field=>value array), value - condition value, table - table, when it is different from current instance, pattern - mask for like operation(array with two values - first and last symbol, default is array('%','%')), join_with - operator for joining condition.

$users = Sdba::table(``'users'``);

$users``->like(``'name'``, 'Bob'``)); // will find 'bob','bobby','bob miller','crazy bob', etc

$user_list = $users``->get();

not_like

Alias of like(), used for convenience and readability.

object not_like( string $field, string $value [, string $table] [, array $pattern] )

and_like

Alias of like(), used for convenience and readability.

object and_like( string $field, string $value [, string $table] [, array $pattern] )

and_not_like

Alias of like(), used for convenience and readability.

object and_not_like( string $field, string $value [, string $table] [, array $pattern] )

or_like

Alias of like(), used for convenience and readability.

object or_like( string $field, string $value [, string $table] [, array $pattern] )

or_not_like

Alias of like(), used for convenience and readability.

object or_not_like( string $field, string $value [, string $table] [, array $pattern] )

where_in

Creates like condition, based on where().

object where_in( string $field, mixed $value [, string $table] )

field - table field (can be string with operator, or field=>value array), value - condition values(can be comma-separated string or array), table - table, when it is different from current instance.

$users = Sdba::table(``'users'``);

$users``->like(``'name'``, 'Bob'``)); // will find 'bob','bobby','bob miller','crazy bob', etc

$user_list = $users``->get();

where_not_in

Alias of where_in**()**, used for convenience and readability.

object where_in( string $field, string $value [, string $table] )

and_in

Alias of where_in**()**, used for convenience and readability.

object and_in( string $field, string $value [, string $table] )

and_not_in

Alias of where_in**()**, used for convenience and readability.

object and_not_in( string $field, string $value [, string $table] )

or_in

Alias of where_in**()**, used for convenience and readability.

object or_in( string $field, string $value [, string $table] )

or_not_in

Alias of where_in**()**, used for convenience and readability.

object or_not_in( string $field, string $value [, string $table] )

open_sub

Allow to create subcondition in where() part (opens '(' in sql query)

object open_sub( [string $join_with] )

join_with - (optional) sets 'glue' between subcondition and other ones in where() side of query, default is 'AND'

$users = Sdba::table(``'users'``);

$users``->where(``'name'``, 'Bob'``));

$users``->open_sub();

$users``->where(``'name'``, 'Bob'``));

$users``->or_where(``'name'``, 'Jack'``));

$users``->close_sub();

$user_list = $users``->get();

close_sub

Closes sub condition.

object close_sub( void )

and_sub()

Alias of open_sub( 'AND' ), used for convenience and readability.

object and_sub( void )

or_sub()

Alias of open_sub( 'OR' ), used for convenience and readability.

object or_sub( void )

fulltext

Creates fultext search (working with fulltext index only)

object fulltext( mixed $fields, string $phrase [, string $mode] [, string $table] )

fields - comma-separated string or array of fieldnames, phrase - search string, mode - search mode, default is natural (natural language mode), can be boolean (boolean mode) or expansion (natural language mode with query expansion); table - table, when it is different from current instance.

$articles = Sdba::table(``'articles'``);

$articles``->fulltext(``'title,text'``, 'Hollidays with Evanto'``));

$articles_list = $articles``->get();

sort_by

Sorts your result data

object sort_by( string $field [, string $direction] [, string $table] )

field - sort field; direction - sort direction (asc, desc); table - table, when it is different from current instance.

$articles = Sdba::table(``'articles'``);

$articles``->sort_by(``'title'``);

$articles``->sort_by(``'created'``,``'desc'``);

$articles_list = $articles``->get();

random

Randomizing of your result data.

object random( void )

$articles = Sdba::table(``'articles'``);

$articles``->random();

$articles_list = $articles``->get(5); // get 5 random articles

join

Select multiple tables with join fearure.

object join( string $type, string $field, string $join_table, string $join_field [, string $left_table] [, string $alias ] [, mixed $right_fields] [, bool $reverse_fields] )

type - joining type, can be any like LEFT, RIGHT, INNER etc, but better to use alias methods (left_join(), right_join(), inner_join()); field - join field in main table; join_table - joined table; join_field - relation field in joined table; left_table - main table, when it is different from current instance; alias - use alias, if you want to join same table more once; right_fields - selected fields from joined table (see fields() method); reverse for selected fields for joined table (see fields() method).

See examples for left_join, inner_join

left_join

Alias of join**()**, used for convenience and readability.

object left_join( string $field, string $join_table, string $join_field [, string $left_table] [, string $alias ] [, mixed $right_fields] [, bool $reverse_fields] )

$articles = Sdba::table(``'articles'``);

$articles``->left_join(``'cat_id'``,``'categories'``,``'cid'``); // selects articles with categories

$articles_list = $articles``->get();

right_join

Alias of join**()**, used for convenience and readability.

object right_join( string $field, string $join_table, string $join_field [, string $left_table] [, string $alias ] [, mixed $right_fields] [, bool $reverse_fields] )

inner_join

Alias of join**()**, used for convenience and readability.

object inner_join( string $field, string $join_table, string $join_field [, string $left_table] [, string $alias ] [, mixed $right_fields] [, bool $reverse_fields] )

$users = Sdba::table(``'users'``);

$users``->left_join(``'id'``,``'profiles'``,``'user_id'``); // users with profile

$users_list = $users``->get();

group_by

Groups your result data.

object group_by( string $field [, string $table] )

field - group field; table - table, when it is different from current instance.

$users = Sdba::table(``'users'``);

$users``->inner_join(``'id'``,``'articles'``,``'user_id'``);

$users``->group_by(``'id'``); // removes duplicates, when user wrote more one article

$users_list = $users``->get(); // users who wrote articles

sum

Selects sum for selected column

mixed sum(string $field [, string $alias] [, string $table] )

field - selected column, alias - alias of sum for get(), get_one result sets; table - table, when it is different from current instance.

$cart = Sdba::table(``'cart'``);

$total = $cart``->sum('price);

max

mixed max(string $field [, string $alias] [, string $table] )

field - selected column, alias - alias of max for get(), get_one result sets; table - table, when it is different from current instance.

$cart = Sdba::table(``'cart'``);

$max = $cart``->max('price);

min

mixed min(string $field [, string $alias] [, string $table] )

field - selected column, alias - alias of min for get(), get_one result sets; table - table, when it is different from current instance.

$cart = Sdba::table(``'cart'``);

$min = $cart``->min('price);

concat

Concatenate fields in one

mixed concat( mixed $fields , string $alias [, string $separator] [, string $table] )

fields - concatenated fields, can be comma-separated string or array; alias - name of field in result set; separator - values separator, default is comma; table - table, when it is different from current instance.

$users = Sdba::table(``'users'``);

$users``->concat(``'first_name,last_name'``,``'full_name'``,``' '``);

$users_list = $users``->get();

group_concat

Concatenate fields from group in one

mixed group_concat( string $field [, string $alias] [, string $separator] [, string $table] [, bool $distinct] [, string $order_by] [, string $direct] )

field - concatenated column; alias - name of field in result set; separator - values separator, default is comma; table - table, when it is different from current instance; distinct - remove duplicates from group; order_by - column for group sorting; direct - sort direction (asc, desc).

$users = Sdba::table(``'users'``);

$users``->left_join(``'id'``,``'avards_fk'``,``'user_id'``); // fk

$users``->inner_join(``'avard_id'``,``'avards'``,``'id'``,``'avards_fk'``); // avards table

$users``->group_concat(``'avard_name'``);

$users``->group_by(``'id'``);

$users_list = $users``->get();

is_unique

Checking value for uniqueness

bool is_unique( string $field, string $value [, string $table] )

field - name of checked field; value - checked value; table - table, when it is different from current instance.

Returns true, when value is uniqueness, or false if not.

$users = Sdba::table(``'users'``);

$bool = $users``->is_unique(``'email'``,``'john@example.com'``); // return true or false

increment

Increment of row(s) value

void increment( string $field [, int $num] [, string $table] )

field - field for action; $num - increment number (default is 1); table - table, when it is different from current instance.

$articles = Sdba::table(``'articles'``);

$articles``->where(``'id'``,55);

$articles``->increment(``'views'``);

decrement

Decrement of row(s) value

void decrement( string $field [, int $num] [, string $table] )

field - field for action; $num - decrement number (default is 1); table - table, when it is different from current instance.

$users = Sdba::table(``'users'``);

$users``->where(``'id'``,118);

$users``->decrement(``'raiting'``,10);

distinct

Add DISTINCT operator in query

object distinct( void )

Method has no parameters

auto_reset

Change autoreset status

object auto_reset( [bool $state] )

state - true to enable, false to disable

$users = Sdba::table(``'users'``);

$users``->auto_reset(true);

reset

Resets conditions (fields, wheres, sort_by, group_by, joins)

object reset( void )

$users = Sdba::table(``'users'``);

...some actions...

$users``->reset();

query

Executes manual query

object query( string $query )

query - sql query.

$db = Sdba::db();

$db``->query(```'TRUNCATE TABLE articles'```); // clear articles table

result

Get result set of manual query as list of arrays

array result()

$db = Sdba::db();

$db``->query(```"SELECT * FROM users"```);

$users = $db``->result();

row

Get one result row of manual query as associative array

array row()

$db = DB::db();

$db``->query(```"SELECT * FROM usersWHEREid = 34"```);

$user = $db``->row();

escape

Escape special chars for safety queries. Adds quotes automatically. Need only for manual queries.

string escape( string $val [, bool $not_qu )

val - string value; not_qu - disable adding quotes when true, default is false

$db = DB::db();

$comment = $db``->escape(``$_POST``[``'comment'``]);

$db``->query(```"INSERT INTO comments (comment) VALUES ({$comment})"```);

abdo-host/Mysqli-DB-Class

Mysqli-DB-Class

First request

Features

System Requirements and Limitations

get

get_one

get_list

get_single

total

delete

insert

insert_id

update

update_fk

set

fields

alias

where

and_where

or_where

like

not_like

and_like

and_not_like

or_like

or_not_like

where_in

where_not_in

and_in

and_not_in

or_in

or_not_in

open_sub

close_sub

and_sub()

or_sub()

fulltext

sort_by

random

join

left_join

right_join

inner_join

group_by

sum

max

min

concat

group_concat

is_unique

increment

decrement

distinct

auto_reset

reset

query

result

row

escape