取出表中改字符串前两位等于01的数据
$pepper_count = $this->orderModel->where("instr(order_id,'02')",2)->count('id');
按主键查询
按主键查用放在括号里
//把按商户和平台的id查出来的数据传到edit前台模板里
$id=input('id');
$agencyRes=db('merchant_agency')
->field('a.*,b.agency_name')
->alias('a')
->join('agency b','a.agency_id=b.id')
->find($id);
不按主键查
$userInfo=db('user')
->field('a.*,b.group_id,c.title')
->alias('a')
->join('role_group_access b','a.uid=b.uid')
->join('role_group c','b.group_id=c.group_id')
->where('a.uid',$uid)
->find();
JOIN方法 的左右连接
JOIN方法也是连贯操作方法之一，用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
$goods_list = db('goods_menu_dishes')
->alias('g')
->join('agency_goods d',"g.menu_id = d.menu_id ",'left') //关联类型。可以为:INNER、LEFT、RIGHT、FULL，不区分大小写，默认为INNER。
->where("g.merchant_id ",$merchant_id)
->where('d.agency_id',$agency_id)
->where('g.menu_id',$menu_id)
->field("g.goods_sn,d.out_sku_id")
->select();
var_dump($goods_list);
die;
not in 方法
$authGroupRes=db('role_group')
->field('group_id,title')
->where('group_id','not in','1,3,4')
->select();
like 查询
use think\Db; //上面需要use
//取出广告栏里title里含有index_banner关键字并且设置为推荐的所有轮播图信息
// $indexModel=new indexModel;
$bannerRes=Db::table('bk_cate')
->field('b.*,a.catename')
->alias('a')
->join('bk_article b','a.id = b.cateid')
->where('catename','like','%轮播图%')
->where('rec','eq',1)
->limit(5)
->select();
where 按条件筛选查询
if (isset($data['station_name']) && empty($data['station_name'])) {
unset($data['station_name']);
} else {
$where['a.station_name'] = ['like', "%" . $data['station_name'] . "%"];
}
if (isset($data['erp_code']) && empty($data['erp_code'])) {
unset($data['erp_code']);
} else {
$where['a.erp_code'] = ['=', $data['erp_code']];
}
if (isset($data['city']) && $data['city'] == '[2,25]') {
$where['a.city'] = ['not in', $data['city']];
} else {
$where['a.city'] = ['=', $data['city']];
}
if (isset($where['a.erp_code']) && empty($where['a.erp_code'])) {
unset($where['a.station_name']);
}
$data = $this->seachAgencyStationiInfo($where);
public function seachAgencyStationiInfo($where = '')
{
//查出所有平台的信息展示出来,供开通平台选用
$agencyInfo = db('agency')->where('display','=',1)->select();
$this->assign('agencyInfo', $agencyInfo);
//查出表格的前半部分显示数据
$shopAgencyInfo = db('shop_station')
->field('a.station_id,a.merchant_id,a.daqu_id,a.erp_code,a.city,a.station_name,a.tel,b.region_name')
->alias('a')
->join('region b', 'a.city=b.region_id')
->where(isset($where) ? $where : '')
// ->fetchSql()
// ->order('station_id desc')
->order('city asc')
->paginate(20);
}
更详情点下面连接
JOIN更多操作