thinkphp6----where查询
几种常见的where查询:
1、关联数组查询
$where = [ 'user_id'=>1, 'phone'=>'18311010011', 'is_delete'=>1 ]; $result = Db::name('user_card')->where($where)->select();
2、索引数组查询
$where = [ ['id','=', 253], ['is_delete','>=', 1], ['phone','like', '%186%'], ['sex','<>', '女'], ['create_time','between',['2020-05-12','2022-01-11']], ]; $result = Db::name('user_card')->where($where)->whereNotNull('birthday')->select();
null 和not null 不能放在where索引数组总查询,会变成比较字符串。
解决方法:用链式函数 ->whereNotNull('birthday') 或者 whereNull(birthday)
$where = [ ['birthday','=','not null'] ]; $result = Db::name('user_card')->where($where)->select();
3、字符串查询:
Db::table('think_user')->whereRaw('type=1 AND status=1')->select();
4、多字段相同条件查询
Db::table('think_user') ->where('name|title','like','thinkphp%') ->where('create_time&update_time','>',0) ->find();
生成的SQL:
SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' OR `title` LIKE 'thinkphp%' ) AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1
5、适用场景查询
a、where(C)->whereOR([A,B]) A和B是OR的关系,但是要和C是AND的关系
如果是这样写:
Db::table('think_user')->where(['is_delete' => 1])->whereOr($where)->select();
这样写,查询出来的就是错的,相当于只有第一个条件。
解决这个就需要用到闭包查询:
Db::table('think_user') ->where(['is_delete' => 1]) ->where(function($query) use ($where){ $query->whereOr($where); }) ->select();
生成的SQL:
SELECT * FROM `un2co_user_card` WHERE `is_delete` = 1 AND ( `nickname` = '白小白' OR `phone` = '18606995547' )
还可以使用字符串查询:
Db::table('think_user') ->where(['is_delete' => 1]) ->whereRaw("nickname='白小白' OR phone='183144444444'") ->select();
打完收工!