包含CRUD,事务,树递归查询,分页等. 演示简单的魅力. 所有的参数都escape了,不存在注入问题.
/*
* Anthony.chen
* 2010 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); //Table|View
$_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).')'