THINKphp中复杂的查询

时间：2022-02-18 22:22:35
  • 字符串拼接查询 
    案例一:拼接字符串(多条件查询)        
    $where = '';   //定义字符串,用于拼接满足条件的数据字段
    
        $value = [];　 // 定义空数组,用于接收值
    
        if(!empty($nickname)){
    
            $where .= ' AND nickname = :nickname'; //数据表字段
    
            $value['nickname'] = $nickname;  //赋值
    
        }
    
        if(!empty($phone)){
    
            $where .= ' AND mobile = :mobile';
    
            $value['mobile'] = $phone;
    
        }
    
        if(!empty($user_status)){
    
            $where .= ' AND user_status = :user_status';
    
            $value['user_status'] = $user_status;
    
        }

        if(!empty($reg_start_end)){
    
            $start_end = explode('|',$reg_start_end);
    
            if(!empty($start_end[0])){
    
                $where .= ' AND create_time > :start_time';
    
                $value['start_time'] = strtotime($start_end[0]);
    
            }
    
            if(!empty($start_end[1])){
    
                $where .= ' AND create_time <= :end_time';
    
                $value['end_time'] = strtotime($start_end[1]);
    
            }
    
        }

        if(!empty($is_proxy)){
    
            $where .= ' AND is_proxy = :is_proxy';
    
            $value['is_proxy'] = $is_proxy;
    
        }
    
        if(!empty($sex)){
    
            $where .= ' AND gender = :gender';
    
            $value['gender'] = $sex;
    
        }$list  = $obj->whereRaw('1=1'.$where.'', $value)->limit($limit_start, $limit_length)->order('create_time', 'asc')->select();  //查询满足条件的数据
    　　     Db::table('表名') ->whereRaw('id = :id AND name LIKE :name ', ['id' => 0, 'name' => 'thinkphp%']) ->select();  //形成的原生sql语句.


    案例二:快捷查询
    快捷查询方式是一种多字段相同查询条件的简化写法，可以进一步简化查询条件的写法，在多个字段之间用|分割表示OR查询，用&分割表示AND查询，可以实现下面的查询，例如：
    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 
    SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' OR `title` LIKE 'thinkphp%' ) AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1;

案例三: 拼接字符串查询
    $where  = '';
      if ($status != 'all') {
            $where .= ' AND status=' . $status;  //拼接满足条件的表字段
    }

相关文章