PHP Model::query方法代码示例

 public function search($para)
 {
     $k = "bs_kt.name like '%" . $para['k'] . "%'";
     //根据实验室分类
     if ($para['d'] == '0') {
         $d = '1=1';
     } else {
         $dep = $para['d'];
         $d = "user_teacher.dep='{$dep}'";
     }
     /**查询sql语句
     		select bs_kt.id,bs_kt.name as bname,bs_kt.snum,user_teacher.name as tname,user_teacher.dep,count(bs_xt.bid) from bs_kt left join user_teacher on user_teacher.user=bs_kt.teacher left join bs_xt on bs_xt.bid=bs_kt.id group by bs_kt.id
     		select bs_kt.id,bs_kt.name as bname,bs_kt.snum,user_teacher.name as tname,user_teacher.dep,count(bs_xt.bid) from bs_kt left join user_teacher on user_teacher.user=bs_kt.teacher left join bs_xt on bs_xt.bid=bs_kt.id where bs_kt.status=1 and bs_kt.name like '%安%' group by bs_kt.id 
     		*/
     $np = 10;
     //每页数
     $cp = (int) $para['p'];
     //当前页
     $sql1 = "select count(*) from bs_kt inner join user_teacher on bs_kt.teacher=user_teacher.user where " . $d . " and " . $k . " and bs_kt.status=3";
     $sql2 = "select bs_kt.id,bs_kt.name as bname,bs_kt.teacher as tuser,user_teacher.name as tname,user_teacher.dep,user_student.user as suser,user_student.name as sname from bs_kt" . " left join user_teacher on bs_kt.teacher=user_teacher.user" . " left join user_student on user_student.user in (select sid from bs_xt where bs_xt.bid = bs_kt.id)" . " where " . $d . " and " . $k . " and bs_kt.status=3 group by bs_kt.id limit " . $np * ($cp - 1) . ',' . $np . '';
     //自定义执行sql语句
     $Model = new \Think\Model();
     $tp = $Model->query($sql1)[0]['count(*)'];
     $rs_kt = $Model->query($sql2);
     $rs = array('page' => array('sql' => $sql2, 'tp' => $tp, 'c_p' => $cp, 't_p' => ceil(1.0 * $tp / $np)), 'r' => $rs_kt ? $rs_kt : array());
     return $rs;
 }

    public function detail()
    {
        $Form = new Model();
        $result = $Form->query('select admin_articles.*, admin_nickname from admin_articles 
			inner join admin_personal on admin_articles.admin_id = admin_personal.admin_id
			where article_id="%s"', $_GET['p']);
        if ($result) {
            $result[0]['article_type'] = C('MODULE_CODE')[$result[0]['article_type']];
            //dump($result[0]);
            if ($result[0]['article_about'] == C(PROJECT_CODE)) {
                $object = $Form->query('select project_name as name, project_logo as portrait from project_info
					where project_id = "%s"', $result[0]['article_object']);
                //dump($object);
                $this->obj = $object[0];
            } else {
                if ($result[0]['article_about'] == C(INVESTOR_CODE)) {
                    $object = $Form->query('select name, portrait from investor_personal
					where user_id = "%s"', $result[0]['article_object']);
                    //dump($object);
                    $this->obj = $object[0];
                } else {
                    if ($result[0]['article_about'] == C(INNOVATOR_CODE)) {
                        $object = $Form->query('select name, portrait from entrepreneur_personal
					where user_id = "%s"', $result[0]['article_object']);
                        //dump($object);
                        $this->obj = $object[0];
                    }
                }
            }
            $this->p = $result[0];
        }
        $update = $Form->execute('update admin_articles set article_visits=%d where article_id="%s"', $result[0]['article_visits'] + 1, $_GET['p']);
        $this->display();
    }

 public function getCitiesByProvince($province)
 {
     $sql = "select a.city as city from t_cities a, t_provinces b\r\n            where b.province like '" . $province . "%' and b.provinceid = a.provinceid";
     $m = new \Think\Model();
     $result = $m->query($sql);
     return $result;
 }

 public function listView($cate)
 {
     $cate_atc = M('cate_atc');
     $count = $cate_atc->where(['status' => 1, 'cate' => $cate])->count();
     $Page = new \Think\Page($count, 16);
     // 实例化分页类 传入总记录数和每页显示的记录数
     $show = $Page->show();
     // 分页显示输出
     $list = $cate_atc->where(['cate' => $cate])->order('createtime')->limit($Page->firstRow . ',' . $Page->listRows)->select();
     $model = new Model();
     foreach ($list as $k => $v) {
         $modelInfo = get_model_info($v['model_id']);
         //获每条数据的模型信息
         //echo $modelInfo['identity'];
         $raw = $model->query("select author from {$modelInfo['identity']} where id = {$v['atc_id']}");
         $list[$k]['author'] = $raw[0]['author'];
         $d = strtotime($v['createtime']);
         $list[$k]['createtime'] = '<div>' . date("Y/m/d", $d) . '</div>' . '<div>' . date("H:i:s", $d) . '</div>';
         //编辑时间格式
     }
     //var_dump($show);
     /* $Page->setConfig('f_decorate','<li>');
        $Page->setConfig('b_decorate','</li>');*/
     $this->assign('page', $show);
     $this->assign('list', $list);
     $this->assign('model_list', get_cate_Model($cate));
     //$this->assign("cate_id",1);
     $this->assign("cate", $cate);
     $this->display();
 }

 public function getFriend($userid)
 {
     $sql = "select u.name,u.id from __USERS__ as u,(select user2 from __FRIEND__ where user1 = {$userid} \r\n               UNION select user1 from  __FRIEND__ where user2 = {$userid}) as f where u.id = f.user2";
     $model = new Model();
     $data = $model->query($sql);
     return $data;
 }

 public function header_city()
 {
     $city1 = new \Think\Model();
     $sql = "select r.* from __PREFIX__store_shipping_region as ssr left join __PREFIX__region as r on ssr.city=r.region_id where ssr.city>0 group by ssr.city";
     $rs = $city1->query($sql);
     var_dump($rs);
 }

 /**
  * 列表显示
  */
 public function index()
 {
     if (!IS_AJAX) {
         $this->error(L('_ERROR_ACTION_'));
     }
     $Model = new Model();
     $islang = $Model->query('Describe __' . strtoupper(CONTROLLER_NAME) . '__ lang');
     if ($islang[0]['field'] == 'lang') {
         if (I('get.lang')) {
             $wl = 'lang=\'' . I('get.lang') . '\'';
         } elseif ($this->clang) {
             $wl = 'lang=\'' . $this->clang . '\'';
         } else {
             $wl = 'lang=\'' . LANG_SET . '\'';
         }
     } else {
         $wl = '1';
     }
     $isorder = $Model->query('Describe __' . strtoupper(CONTROLLER_NAME) . '__ listorder');
     $ol = $isorder == 'listorder' ? 'listorder,' : '';
     $name = M(CONTROLLER_NAME);
     $list = $name->where($wl)->order($ol, 'id')->select();
     $this->assign('list', $list);
     $this->display();
 }

 public function check($para)
 {
     $user = $para['user'];
     $type = $para['type'];
     $pwd = md5($para['pwd']);
     $vcode = $para['verifycode'];
     //首先验证验证码
     if ($this->check_verify($vcode)) {
         //验证账号密码
         switch ($type) {
             case '3':
                 $tb = 'user_admin';
                 $tb2 = 'user_admin';
                 break;
             case '2':
                 $tb = 'user_teacher_pwd';
                 $tb2 = 'user_teacher';
                 break;
             case '1':
                 $tb = 'user_student_pwd';
                 $tb2 = 'user_student';
         }
         $Model = new \Think\Model();
         $sql = "select  * from {$tb2} B left join {$tb} A on A.user=B.user where A.user='{$user}' and A.pwd='{$pwd}' and B.status=1";
         //var_dump($sql);
         if (count($Model->query($sql))) {
             return 1;
         } else {
             return 0;
         }
     } else {
         return -1;
     }
 }

 public function index($cate)
 {
     $cate_atc = M('cate_atc');
     $count = $cate_atc->where(['status' => 1, 'cate' => $cate])->count();
     $Page = new \Think\Page($count, 16);
     // 实例化分页类 传入总记录数和每页显示的记录数
     $show = $Page->show();
     // 分页显示输出
     $list = $cate_atc->where(['cate' => $cate])->order('createtime')->limit($Page->firstRow . ',' . $Page->listRows)->select();
     $model = new Model();
     foreach ($list as $k => $v) {
         $modelInfo = get_model_info($v['model_id']);
         //获每条数据的模型信息
         $raw = $model->query("select author from {$modelInfo['identity']} where id = {$v['atc_id']}");
         $list[$k]['author'] = $raw[0]['author'];
         $d = strtotime($v['createtime']);
         $list[$k]['createtime'] = '<h6>' . date("m/d", $d) . '</h6>';
         //编辑时间格式
     }
     $this->assign('page', $show);
     $this->assign('list', $list);
     $this->assign('model_list', get_cate_Model($cate));
     $this->assign("cate", $cate);
     $this->display('News/index');
 }

 function checkCoachMaxUsers()
 {
     $param = json_decode(file_get_contents('php://input'), true);
     $token = $param['xtoken'];
     // init_verify_token($token);
     $coachid = $param['coachid'];
     // $coachid = 53;
     if (empty($coachid)) {
         err_ret(-205, 'lack of param', '缺少参数');
     }
     //判断教练的客户是否超过10个
     $model = new Model();
     $sql_user = 'SELECT DISTINCT uid  FROM my_plan WHERE coachid=' . $coachid;
     $result_user = $model->query($sql_user);
     $studentNums = M("user_info")->where("type=1 and id={$coachid}")->getField("studentnums");
     if (count($result_user) >= $studentNums) {
         $model_user = new Model('user_info');
         $save_data['status'] = 1;
         $where_data['id'] = $coachid;
         $where_data['type'] = 1;
         $model_user->where($where_data)->save($save_data);
         $data['errno'] = 0;
         $data['is_max'] = 1;
         $data['coachid'] = $coachid;
         echo json_encode($data);
     } else {
         $data['errno'] = 0;
         $data['is_max'] = 0;
         $data['coachid'] = $coachid;
         echo json_encode($data);
     }
 }

function getTableInfoArray($tableName)
{
    $dbType = C('DB_TYPE');
    $Model = new Model();
    // 实例化一个model对象 没有对应任何数据表
    if ($dbType == 'mysql') {
        $dbName = C('DB_NAME');
        $result = $Model->query("select * from information_schema.columns where table_schema='" . $dbName . "' and table_name='" . C('DB_PREFIX') . $tableName . "'");
        return $result;
    } else {
        //sqlite
        $result = $Model->query("pragma table_info (" . C('DB_PREFIX') . $tableName . ")");
        return $result;
    }
    $this->error('数据库类型不支持');
}

 function getCategory()
 {
     /*	$data['slug'] <> 'uncategorized';
        return $this->where($data)->select();*/
     $Model = new Model();
     return $Model->query("select * from wp_terms where wp_terms.slug <> 'uncategorized'");
 }

 public function login()
 {
     $Form = new Model();
     $user = $Form->query("select * from admin_personal where admin_id = '%s'", $_POST['loginuser']);
     //echo  json_encode($user[0]);
     if ($user[0]['admin_pwd'] === $_POST['loginpwd']) {
         $_SESSION['userid'] = $user[0]['admin_id'];
         //session(array('name'=>'userid','expire'=>2),$user[0]['admin_id']);
         $_SESSION['usertype'] = $user[0]['admin_type'];
         if ($_SESSION['usertype'] == 1) {
             echo 201;
         } else {
             if ($_SESSION['usertype'] == 2) {
                 echo 202;
             } else {
                 if ($_SESSION['usertype'] == 3) {
                     echo 203;
                 } else {
                     echo 404;
                 }
             }
         }
     } else {
         echo 409;
     }
 }

 public function searching()
 {
     $model = new Model();
     $sql = "use  " . $_SESSION['dbName'];
     $model->query($sql);
     $shop_id = trim($_GET['shopid']);
     if ($shop_id == "") {
         $shop_id = $_SESSION["shop_id"];
     }
     if ($shop_id == "") {
         $this->error("请先点击具体的店铺，再回来点此");
     }
     $this->assign("shopid", $shop_id);
     $shopName = $_SESSION["shop_name"];
     $this->assign("shopName", $shopName);
     $this->assign("shop", U('Retailer/index', array('shopid' => $shop_id)));
     if (!IS_GET) {
         $this->display();
     } else {
         $man = trim($_GET['man']);
         $date = trim($_GET['date']);
         if ($man != "") {
             $where['realName'] = array('like', "%{$man}%");
             $this->assign("man", $man);
         }
         $start = trim($_GET['start']);
         $end = trim($_GET['end']);
         if ($end != "") {
             $where['saledate'] = array('between', array($start . " 00:00:00", $end . " 59:59:59"));
             $this->assign("date", $people);
         }
         $where['belong'] = $_SESSION['auth'];
         $everypage = "10";
         //$res = $out->field("id,outtime,outprice,outamount")->relation(true)->order("outtime desc")->select();
         // $tbName = D("RetailerView"); // 实例化Data数据对象
         $tbName = new RetailerViewModel("RetailerViewModel", $tablePrefix = $shop_id);
         /*
          *  使用视图子查询
          */
         $subQuery = $tbName->group('name')->where($where)->order('sum(amount) desc')->select(false);
         $count = $tbName->table($subQuery . 'xc')->count('name');
         //$count = $tbName->count('barcode'); // 查询满足要求的总记录数
         $Page = new Page($count, $everypage);
         // 实例化分页类 传入总记录数和每页显示的记录数(25)
         $show = $Page->show();
         // 分页显示输出// 进行分页数据查询 注意limit方法的参数要使用Page类的属性
         $list = $tbName->field("sum(amount) as total,name,realName")->where($where)->order("sum(amount) desc")->limit($Page->firstRow . ',' . $Page->listRows)->group("name")->select();
         $this->assign('list', $list);
         // 赋值数据集
         $this->assign('page', $show);
         // 赋值分页输出
         // $tbName = D('RetailerView');
         $sumSQL = $tbName->field('sum(amount) as total')->where($where)->order("sum(amount) desc")->group("name")->select(FALSE);
         $outsum = $tbName->table($sumSQL . 'cc')->sum('total');
         $this->assign("outsum", $outsum);
         $this->display();
     }
 }

 public function getNews()
 {
     //$model_news = M('home_news');
     //$rs_news = $model_news->limit(0,5)->select();
     $model_news = new \Think\Model();
     $sql = "select * from home_news order by publishtime desc limit 0,5";
     $rs_news = $model_news->query($sql);
     return $rs_news;
 }