- php数据库
- tp6 union的方法+子查询
-
$query1 = Db::name('rowcard')->alias('c') ->field('c.*, m.nickname as customer_name, mm.nickname as platform_name, mmm.nickname, 1 as type') ->leftJoin('member m', 'c.enter_customer_mid = m.id') ->leftJoin('member mm', 'c.enter_platform_mid = mm.id') ->leftJoin('member mmm', 'c.card_mid = mmm.id') ->where($where) ->group('c.enter_customer_mid') ->order($order) ->buildSql(); // 构建子查询 SQL $query2 = Db::name('rowcard')->alias('c') ->field('c.*, m.nickname as customer_name, mm.nickname as platform_name, mmm.nickname, 2 as type') ->leftJoin('member m', 'c.enter_customer_mid = m.id') ->leftJoin('member mm', 'c.enter_platform_mid = mm.id') ->leftJoin('member mmm', 'c.card_mid = mmm.id') ->where($where) ->group('c.enter_platform_mid') ->order($order) ->buildSql(); // 构建子查询 SQL // 使用 UNION ALL 合并两个子查询 写法一 $data = Db::table("($query1) union_table") ->unionAll("($query2)") ->limit($limit) ->page($page) ->select()->toArray(); // 使用 unionAll() 方法合并两个子查询 $subQuery = Db::table("($query1) union_table") ->unionAll("($query2)") ->buildSql(); // 查询总数 $count = Db::table("$subQuery union_result")->count(); // 查询实际数据 写法二 $data = Db::table("$subQuery union_result") ->limit($limit) ->page($page) ->select() ->toArray();
- 根据用户表去查每个用户的资产币种表下每个币种的总和的 币种有重复
- 核心:数据库column直接返回数据列,group分组查询,in把重复的数组查询后自动去除重复数据
-
方法一: $member = Db::name('member')->where('aid',aid)->select()->toArray(); $title ='币种余额'; $data = array(); foreach($member as $k => $v){ // 各个币种有重复 //资产币种表 member_money_currency $money_curr = Db::name('member_money_currency')->field('currid')->where('aid',aid)->where('mid',$v['id'])->select()->toArray(); if(!$money_curr)continue; $arr = []; foreach($money_curr as $v){ $arr[] = $v['currid']; } $filter = array_unique($arr); $content = []; foreach($filter as $kk => $vv){ // 货币表currency $currname = Db::name('currency')->where('aid',aid)->where('id', $vv)->find(); $money = 0+Db::name('member_money_currency')->where('currid',$vv)->where('aid',aid)->sum('balance'); $content[] = $currname['name'].'余额:'.$money; } $end = implode(",",$content); $data[] = $end; } 方法二: $member = Db::name('member')->where('aid',aid)->select()->toArray(); foreach($member as $k => $v){ // 获取币种信息及总金额 $money_curr = Db::name('member_money_currency')->where('aid', aid)->where('mid', $v['id'])->column('currid'); // 使用column方法直接获取currid数组 if (empty($money_curr)) { continue; } $money_info = Db::name('member_money_currency')->where('currid', 'in', $money_curr)->where('aid', aid)->group('currid')->column('currid,sum(balance) as total_money'); // 一次性获取每个币种的总金额 $currency_names = Db::name('currency')->where('aid', aid)->where('id', 'in', $money_curr)->column('id, name'); // 一次性获取所有币种的名称 $content = []; foreach ($money_info as $kk=> $curr_id) { $curr_name = isset($currency_names[$kk]['name']) ?$currency_names[$kk]['name'] : 'Unknown'; // 获取币种名称 $total_money = isset($money_info[$curr_id]) ? $money_info[$curr_id] : 0; // 获取币种总金额 $content[] = $curr_name . '总金额:' . $total_money; } }
- php中对于数据比较大的接口传递参数
- set_time_limit(0); // 运行时间无限
- ini_set('memory_limit', -1);//运行时内存占用无上限
-
<?php // +---------------------------------------------------------------------- // | 自动执行 每分钟执行一次 crontab -e 加入 */1 * * * * curl https://域名/?s=/ApiAuto/index/key/配置文件中的authtoken // +---------------------------------------------------------------------- namespace app\controller; use app\BaseController; use think\facade\Db; use think\facade\Log; class ApiAuto extends BaseController { public function initialize(){ } public function index(){ $config = include(ROOT_PATH.'config.php'); set_time_limit(0); // 运行时间无限 ini_set('memory_limit', -1);//运行时内存占用无上限 if(input('param.key')!=$config['authtoken']) die('error'); $this->perminute();//需要每分钟执行的函数 die; } public function perminute();{}
- 子类调用父类的方法
-
父类 <?php namespace app\controller; use think\facade\Db; class ApiCommon { public function getLanguageInfo(){ 相关逻辑代码 } } 子类 <?php namespace app\controller; use think\facade\Db; class ApiShop extends ApiCommon { // 第一种 在一个function下调用了,全部类中可以调用$this->language public $language; public function getLanguage(){ 调用父类的方法 $this->language = $this->getLanguageInfo(); } //第二种 使用前提:父类中继承了或父类的父类继承了 extends BaseController initialize 是 BaseController的验证方法 public $language; public function initialize(){ parent::initialize();使用父类的initialize,initialize 在tp6中会自动执行 $this->language = $this->getLanguageInfo(); } }
- 设置全局常量
- tips:注意define作用域,可用defined检验
// 在中公共控制器里定义 define(常量,值) (public $常量可不加)
<?php
namespace app\controller;
use app\BaseController;
use think\facade\Db;
class ApiBase extends BaseController
{
public $aid;
public $mid;
public function initialize(){
$aid = input('param.aid/d');
if(!$aid) die(jsonEncode(['status'=>0,'msg'=>'参数错误']));
$platform = input('param.platform');
define('platform',$platform);
$this->aid = $aid;
define('aid',$aid);
}
}
- tp的多表查询join与where的使用
public function index(){
if(request()->isAjax()){
$page = input('param.page');
$limit = input('param.limit');
if(input('param.field') && input('param.order')){
$order = input('param.field').' '.input('param.order');
}else{
$order = 'id desc';
}
$where = array();
$where[] = ['ddwx_currency.aid','=',aid];
if(input('param.name')) $where[] = ['ddwx_currency.name','like','%'.input('param.name').'%'];
if(input('?param.status') && input('param.status')!=='') $where[] = ['ddwx_currency.status','=',input('param.status')];
//dump($where);
$count = 0 + Db::name('currency')->where($where)->count();
$data = Db::table('ddwx_currency')
->join('ddwx_currency_type','ddwx_currency.ctid = ddwx_currency_type.id')
->field('ddwx_currency_type.name as uname,ddwx_currency.*')
->where($where)->page($page,$limit)->order($order)->select()->toArray();
return json(['code'=>0,'msg'=>'查询成功','count'=>$count,'data'=>$data]);
}
return View::fetch();
}
如图多表后where语句想要在查询前加入数据表名
- thinkphp6获取ip
use think\facade\Request;
// 获取客户端IP地址
$clientIP = Request::ip();
$clientIP = request->ip();
// 获取服务器IP地址
$serverIP = gethostbyname(gethostname());
// 获取网络IP地址
$networkIP = Request::server('SERVER_ADDR');
$networkIP = request()->server('SERVER_ADDR');
- 缓存cache
// 存储
cache(名字,值,过期时间);
// 获取
$变量 = cache(名字)