數據庫構造器 where · QueryPHP V4.0

# 函數原型 ~~~ public function where($mixCond /* args */); ~~~ # 字段（表達式）值 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` = 1 database::table ( 'test' )->where ( 'id', '=', 1 )->getAll (); # SELECT `test`.* FROM `test` WHERE `test`.`id` = 2 database::table ( 'test' )->where ( 'id', 2 )->getAll (); # SELECT `test`.* FROM `test` WHERE `test`.`id` = 2 AND `test`.`name` > '狗蛋' AND `test`.`value` LIKE '小鴨子' database::table ( 'test' )->where ( 'id', 2 )->where ( 'name', '>', '狗蛋' )->where ( 'value', 'like', '小鴨子' )->getAll (); ~~~ # 以數組直接傳入 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`name` LIKE '技術' database::table ( 'test' )->where ( ['name','like', '技術'] )->getAll (); # SELECT `test`.* FROM `test` WHERE `test`.`name` LIKE '技術' AND `test`.`value` <> '結局' database::table ( 'test' )->where ([['name','like', '技術'], ['value','<>', '結局'] ])->getAll (); ~~~ # OrWhere 語法 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`name` LIKE '技術' database::table('test')->where('name','like', '技術')->orWhere('value','<>', '結局')->getAll(); ~~~ # whereBetween 語法 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` BETWEEN 1 AND 100 database::table('test')->whereBetween('id', [1, 100])->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` BETWEEN 1 AND 10 database::table('test')->where('id','between', [1,10])->getAll(); ~~~ # whereBetween 二維數組語法支持后面的語法均支持這樣的寫法，后面不再繼續示例了。 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` BETWEEN 1 AND 100 AND `test`.`name` BETWEEN 5 AND 22 database::table('test')->whereBetween( [ ['id', [1, 100]], ['name', [5, 22]] ])->getAll(); ~~~ # whereNotBetween 語法 ~~~ #SELECT `test`.* FROM `test` WHERE `test`.`id` NOT BETWEEN 1 AND 10 database::table('test')->whereNotBetween('id', [1,10])->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` NOT BETWEEN 1 AND 10 database::table('test')->where('id','not between', [1,10])->getAll(); ~~~ # whereIn 語法 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` IN (2,50) database::table('test')->whereIn('id', [2,50])->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` IN ('1','10') database::table('test')->where('id','in', '1,10')->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` IN (2,50) database::table('test')->where('id','in', [2,50])->getAll(); ~~~ # whereNotIn 語法 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` NOT IN (2,50) database::table('test')->whereNotIn('id', [2,50])->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` NOT IN ('1','10') database::table('test')->where('id','not in', '1,10')->getAll(); ~~~ # whereNull 語法 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` IS NULL database::table('test')->whereNull('id')->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` IS NULL database::table('test')->where('id','null')->getAll(); ~~~ # whereNotNull 語法 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` IS NOT NULL database::table('test')->whereNotNull('id')->getAll(); # SELECT `test`.* FROM `test` WHERE `test`.`id` IS NOT NULL database::table('test')->where('id','not null')->getAll(); ~~~ # whereExists 語法 ~~~ # SELECT `test`.* FROM `test` WHERE EXISTS ( SELECT `subsql`.* FROM `subsql` WHERE `subsql`.`id` = 1 ) database::table ( 'test')->whereExists ( function($objSelect){ $objSelect->table ( 'subsql')->where('id',1); } ) ->getAll (); # SELECT `test`.* FROM `test` WHERE EXISTS ( SELECT `subsql`.* FROM `subsql` ) $objSubSelect = database::table ( 'subsql'); database::table ( 'test')->where ( [ 'exists__' => $objSubSelect ] ) ->getAll (); # SELECT `test`.* FROM `test` WHERE EXISTS ( select *from d_sub ) database::table ( 'test')->where ( [ 'exists__' => 'select *from d_sub' ] ) ->getAll (); # SELECT `test`.* FROM `test` WHERE EXISTS ( SELECT `subsql`.* FROM `subsql` WHERE `subsql`.`id` = 1 ) database::table ( 'test')->where ( [ 'exists__' => function($objSelect){ $objSelect->table ( 'subsql')->where('id',1); } ] ) ->getAll (); ~~~ # 分組查詢 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` = '5' OR (`test`.`votes` > 100 AND `test`.`title` <> 'Admin') database::table ( 'test' )->where ( 'id', 5 )->orWhere ( function ($objSelect) { $objSelect->where ( 'votes', '>', 100 )->where ( 'title', '<>', 'Admin' ); } )->getAll (); # SELECT `test`.* FROM `test` WHERE `test`.`id` = '5' OR `test`.`name` = '小牛' AND (`test`.`votes` > 100 OR `test`.`title` <> 'Admin') database::table ( 'test' )->where ( 'id', 5 )->orWhere ( 'name', '小牛' )->where ( function ($objSelect) { $objSelect->where ( 'votes', '>', 100 )->orWhere ( 'title', '<>', 'Admin' ); } )->getAll (); ~~~ # 條件表達式支持條件表達式由“{}”包起來表示支持條件表達式，“[]”表示支持字段格式化。 ~~~ # SELECT `test`.`post`,`test`.`value`,concat("tt_",`test`.`id`) FROM `test` WHERE concat("hello_",`test`.`posts`) = `test`.`id` database::table ( 'test', 'post,value,{concat("tt_",[id])}' )->where ( '{concat("hello_",[posts])}', '=', '{[id]}' )->getAll (); ~~~ # 字段作為鍵值 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`id` = '故事' AND `test`.`name` IN (1,2,3) AND `test`.`weidao` BETWEEN '40' AND '100' AND `test`.`value` IS NULL AND `test`.`remark` IS NOT NULL AND `test`.`goods` = '東亞商品' AND `test`.`hello` = 'world' database::table ( 'test' )->where ( [ 'id' => [ '=','故事' ], 'name' => ['in',[1,2,3]], 'weidao' => ['between','40,100'], 'value' => 'null', 'remark' => ['not null'], 'goods' => '東亞商品', 'hello' => ['world'] ] )->getAll (); ~~~ # string__ 支持（支持字段） ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`name` = 11 and `post`.`value` = 22 and concat("tt_",`test`.`id`) database::table ( 'test')->where ( ['string__' => '{[name] = 11 and [post.value] = 22 and concat("tt_",[id])}'] )->getAll (); ~~~ # subor__ 和 suband__ 支持 ~~~ # SELECT `test`.* FROM `test` WHERE `test`.`hello` = 'world' OR (`test`.`id` LIKE '你好') database::table ( 'test')->where ( [ 'hello' => 'world', 'subor__' => ['id','like','你好'] ] ) ->getAll (); # SELECT `test`.* FROM `test` WHERE `test`.`hello` = '111' OR (`test`.`id` LIKE '你好' AND `test`.`value` = 'helloworld') AND (`test`.`id` LIKE '你好' OR `test`.`value` = 'helloworld' OR (`test`.`child_one` > '123' AND `test`.`child_two` LIKE '123')) database::table ( 'test')->where ( [ 'hello' => '111', 'subor__' => [ ['id','like','你好'], ['value','=','helloworld'] ], 'suband__' => [ 'logic__' => 'or', ['id','like','你好'], ['value','=','helloworld'], 'subor__' => [ ['child_one','>','123'], ['child_two','like','123'], ] ] ] ) ->getAll (); ~~~