A good MySQL class

Contains crud, transactions, tree recursive queries, paging, and more. Demonstrate the simplicity of glamour. All parameters are escape, there is no injection problem.

/*

* Anthony.chen

* Reserved

*/

Class DB {

Query types

Const SELECT = 1;

Const INSERT = 2;

Const UPDATE = 3;

Const DELETE = 4;

Const T = ' t ';

Const F = ' F ';

/*

* Supporting return multiple data;

*/

public static function Insert_table ($table _name, $data, $return _id= ' id ', $db = ' default ') {

if (!is_array ($data)) {

return false;

}

if (Is_null ($return _id)) {

Self::query (Self::select,self::insert ($table _name,array_keys ($data))->values (Array_values ($data))-> Execute ($DB);

return true;

}

if (is_string ($return _id)) {

$id = Self::query (Self::select,self::insert ($table _name,array_keys ($data))->values (Array_values ($data)). " Returning ". $return _id)->execute ($db)->get ($return _id);

return $id;

}else{

if (Is_array ($return _id)) {

$ids = Implode (', ', $return _id);

$r _ids = Self::query (Self::select,self::insert ($table _name,array_keys ($data))->values (Array_values ($data)). " Returning ". $ids)->execute ($db)->current ();

return $r _ids;

}

}

return false;

}

public static function Update_table ($table _name, $id, $data, $id _name= ' id ', $db = ' default ') {

if (!is_array ($data)) {

return false;

}

Return Self::update ($table _name)->set ($data)->where ($id _name, ' = ', $id)->execute ($DB);

}

public static function quote ($s, $db = ' default ') {

if (!is_array ($s)) {

Return Database::instance ($db)->quote ($s);

}else{//quote Array and implode

$_qs = Array ();

foreach ($s as $ele) {

$_qs[] = Self::quote ($ele, $db);

}

$_quotestring = Implode (', ', $_QS);

return $_quotestring;

}

}

public static function Escape ($s, $db = ' default ') {

Return Database::instance ($db)->escape ($s);

}

public static function quote_table ($s, $db = ' default ') {

Return Database::instance ($db)->quote_table ($s);

}

public static function getconnection ($db = ' default ') {

Return Database::instance ($DB)->getconnection ();

}

public static function GetChildren ($table, $returnSql = false, $pid = ' 0 ', $idname = ' id ', $pidname = ' pid ', $db = ' default ') {

$_sql = ' select * from '. $table. ' WHERE '. $pidname. ' = '. Self::escape ($pid, $db).

"and $idname <>". Db::escape ($pid, $db);

if ($RETURNSQL) {

return $_sql;

}

$_res = Self::query (self::select,$_sql)->execute ($db)->as_array ();

if ($_res) {

return $_res;

}else{

return false;

}

}

/*

*

*/

public static function Gettree ($tableName, $returnSql =false, $startWith = ' 0 ', $idCol = ' id ', $pidCol = ' pid ', $orderCol = ' Id ', $maxDepth =0, $level = 0, $delimiter = '; ', $db = ' default ') {

$_funcparas = Array ();

$_funcparas[] = Self::quote ($tableName, $db); TableView

$_funcparas[] = Self::quote ($idCol, $db); ID column

$_funcparas[] = Self::quote ($pidCol, $db); Parent ID Column

$_funcparas[] = Self::quote ($orderCol, $db); Default ORDER BY ASC

$_funcparas[] = Self::quote ($startWith, $db); Begin NODE

$_funcparas[] = Self::quote ($maxDepth, $db); Begin Depth of Traverse

$_funcparas[] = Self::quote ($delimiter, $db); Delimitor,default '; '

$_sql = ' SELECT * from Connectby ('

. Implode (', ', $_funcparas). ') '

. ' As t (id int, PID int, level int, branch text, pos int) ';

if ($level > 0) {

$_sql. = ' Where level >= '. Self::quote ($level);

}

if ($RETURNSQL) return $_sql;

$_res = Self::query (self::select,$_sql,true)->execute ($db)->as_array ();

if ($_res) {

return $_res;

}else{

return false;

}

}

public static function begin ($db = ' default ') {

Db::query (Database::update, "BEGIN")->execute ($DB);

}

public static function commit ($db = ' default ') {

Db::query (Database::update, "COMMIT")->execute ($DB);

}

public static function rollback ($db = ' default ') {

Db::query (Database::update, "ROLLBACK")->execute ($DB);

}

/**

* Create a new [database_query] of the given type.

*

*//Create a new SELECT query

* $query = Db::query (Database::select, ' SELECT * from users ');

*

*//Create a new DELETE query

* $query = Db::query (Database::D elete, ' DELETE from users WHERE id = 5 ');

*

* Specifying the type changes the returned result. When using

* ' Database::select ', a [Database_query_result] 'll be returned.

* ' Database::insert ' queries'll return the INSERT ID and number of rows.

* For all other queries, the number of affected rows are returned.

*

* @param integer type:database::select, database::update, etc

* @param string SQL statement

* @return Database_query

*/

public static function query ($type, $sql, $as _object = False)

{

return new Database_query ($type, $sql, $as _object);

}

/**

* Create a new [Database_query_builder_select]. Each argument would be

* Treated as a column. To generate a ' foo as bar ' Alias, use an array.

*

*//SELECT ID, username

* $query = db::select (' id ', ' username ');

*

*//SELECT ID as user_id

* $query = db::select (Array (' ID ', ' user_id '));

*

* @param mixed column name or array ($column, $alias) or object

* @param ...

* @return Database_query_builder_select

*/

public static function Select ($columns = NULL)

{

return new Database_query_builder_select (Func_get_args ());

}

/**

* Create a new [Database_query_builder_select] from an array of columns.

*

*//SELECT ID, username

* $query = Db::select_array (Array (' ID ', ' username '));

*

* @param array columns to select

* @return Database_query_builder_select

*/

public static function Select_array (array $columns = NULL)

{

return new Database_query_builder_select ($columns);

}

/**

* Create a new [Database_query_builder_insert].

*

*//INSERT into users (ID, username)

* $query = Db::insert (' users ', array (' ID ', ' username '));

*

* @param string table to insert into

* @param array List of column names or array ($column, $alias) or object

* @return Database_query_builder_insert

*/

public static function Insert ($table = null, array $columns = null)

{

return new Database_query_builder_insert ($table, $columns);

}

/**

* Create a new [database_query_builder_update].

*

*//UPDATE Users

* $query = db::update (' users ');

*

* @param string table to update

* @return Database_query_builder_update

*/

public static function Update ($table = NULL)

{

return new Database_query_builder_update ($table);

}

/**

* Create a new [Database_query_builder_delete].

*

*//DELETE from users

* $query = DB::d elete (' users ');

*

* @param string table to delete from

* @return Database_query_builder_delete

*/

public static function Delete ($table = NULL)

{

return new Database_query_builder_delete ($table);

}

/**

* Create a new [database_expression] which is not escaped. An expression

* is the only way to use SQL functions within query builders.

*

* $expression = db::expr (' COUNT (users.id) ');

*

* @param string expression

* @return Database_expression

*/

public static function expr ($string) {

return new Database_expression ($string);

}

/*

* gettting paginated page

*/

public static function GetPage ($_sql,& $page, $orderBy = ' Updated desc ', $dataPro = ' data ', $pagePro = ' pagination ',

$config = NULL, $db = ' default ', $as _object= true) {

$_csql = ' Select count (1) as C from ('. $_sql. ') St ';

$_c = Db::query (db::select,$_csql)->execute ($db)->get (' C ');

if ($config) {

$config [' total_items '] = $_c;

$_pagination = new pagination ($config);

}else{

$config = Array ();

$config [' total_items '] = $_c;

$_pagination = new pagination ($config);

}

$_sql. = ' ORDER by '. $orderBy;

if ($_pagination->offset) {

$_sql. = ' Offset ' .$_pagination->offset;

}

$_sql. = ' Limit ' .$_pagination->items_per_page;

$_data = Db::query (Db::select,$_sql, $as _object)->execute ($db)->as_array ();

if (!$_data) {

$page->{$dataPro} = false;

$page->{$pagePro} = false;

return false;

}

$page->{$dataPro} = $_data;

$page->{$pagePro} = $_pagination;

return true;

}

/*

* Get all roles

* Level to control the STATRT

*/

public static function Getroletreesql ($role _id, $quote = False, $role _table, $level =0, $db = ' default ') {

$_sql = ' Select id from ('. Self::gettree ($role _table,true, $role _id, ' id ', ' pid ', ' id ',

0,//maxdepth

$level,//level.

'; ', $db). ' Utree ';

if (! $quote) return $_sql;

else return ' ('. $_sql. ') ';

}

/*

* Return sub query on Objects authorization

* Child role objects and owned objects

* Parent Control

*/

public static function Getcurtreesql ($role _id, $user _id, $role _table, $quote = True,

$role = ' role_id ', $owner = ' owner_id ', $db = ' default ') {

$_sql = '. $role. Self::getroletreesql ($role _id,true, $role _table,

1,//level start with 1

$DB). ' or '. $owner. ' ='. Db::quote ($user _id);

if (! $quote) return $_sql;

else return ' ('. $_sql. ') ';

}

}