ThinkPHP5 method of querying data and processing results

This article mainly introduces the method of querying data and processing results in ThinkPHP5. It summarizes and analyzes common query statements and three ways of querying the database in thinkPHP5 based on examples. Friends in need can refer to the following

Examples of this article Learn how to query data and process results using ThinkPHP5. Share it with everyone for your reference. The details are as follows:

I encountered some problems when processing database query results. Record the several query methods and result processing used.

1. Query a certain record

$where=array(
  "version_id"=>$version_id
);
$data = model("PackageWhitelist")->where($where)->find();
$this->assign("package_id",$package_id);
$where=array(
  "package_id"=>$package_id
);
$data = model("Package")->where($where)->find();
if($data){
  $this->assign("target_version",$data['target_version']);
}

2. Query a certain field of a certain record

$device_number_list = model("PackageWhitelist")->where($where)->field("device_number")->find();

$this->assign("device_number",$device_number_list['device_number']);

3. Query a certain field of multiple records and process the results. The result is an array set

$where=array(
     "version_id"=>$version_id
 );
$data = model("PackageWhitelist")->where($where)->field("device_number")->select();
$device_number_list='';
foreach($data as $val){
  $list = $val->toArray();
  if($device_number_list){
    $device_number_list=$device_number_list.';'.$list["device_number"];
  }else{
    $device_number_list=$list["device_number"];
  }
}

4. Query multiple records

$where=array(
  "version_id"=>$version_id
);
$data = model("PackageWhitelist")->where($where)->select();
$device_number_list='';
foreach($data as $val){
  $list = $val->toArray();
  if($device_number_list){
    $device_number_list=$device_number_list.';'.$list["device_number"];
  }else{
    $device_number_list=$list["device_number"];
  }
}

5. Query in page format and process the results.

public function index($version_id){
  $where=array(
    "version_id"=>$version_id
  );
  $version_name = model("Version")->where($where)->field("version_name")->find();
  $listrows=config("LISTROWS")?config("LISTROWS"):10;
  $package_lists=model("Package")->where($where)->paginate($listrows);
  $package_infos = $package_lists->toArray()["data"];
  foreach($package_infos as $key=>$value){
    $package_infos[$key] = array("source_version" => $version_name["version_name"]) + $package_infos[$key];
  }
}

Let’s summarize the three ways of querying the database in TP5

Method 1: Native sql query

Code example:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/8
 * Time: 下午2:15
 */
namespace app\api\model;
use think\Db;
use think\Exception;
class Banner
{
  public static function getBannerByID($id){
    $result = Db::query(&#39;select * from banner_item where banner_id=?&#39;,[$id]);
    return $result;
  }
}

Method 2: Using the query builder

Code example:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/8
 * Time: 下午2:15
 */
namespace app\api\model;
use think\Db;
use think\Exception;
class Banner
{
  public static function getBannerByID($id){
    //1.使用原生sql
//    $result = Db::query(&#39;select * from banner_item where banner_id=?&#39;,[$id]);
//    return $result;
    //2.使用查询构建器
    /*
     * 链式查询Db::table(&#39;banner_item&#39;)->where(&#39;banner_id&#39;,&#39;=&#39;,$id) 返回查询对象,->select();返回查询结果,
     * 除了select操作还有 find(返回一条数据) update delete insert
     * 对应的where 也分三种,1.表达式where(&#39;字段名&#39;,&#39;表达式&#39;,&#39;查询条件&#39;) 2.数组发 3.闭包。
     */
    // 2.1 表达式法
//    $result = Db::table(&#39;banner_item&#39;)
//      ->where(&#39;banner_id&#39;,&#39;=&#39;,$id)
//      ->select();
//    return $result;
    //2.2 闭包法
    $result = Db::table(&#39;banner_item&#39;)
      ->where(function ($query) use($id){
        $query->where(&#39;banner_id&#39;,&#39;=&#39;,$id);
      })
      ->select();
    return $result;
  }
}

Method Three: ORM (Object Relation Mapping) Object Relational Mapping

The main difference in using ORM to query the database is to write the model's inherited think\model class, and then the controller can use the model's default method. Obtain the data instead of writing a dedicated acquisition method in the model

Code example:

model:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/8
 * Time: 下午2:15
 */
namespace app\api\model;
use think\Db;
use think\Model;
class Banner extends Model
{
//  public static function getBannerByID($id){
//    //1.使用原生sql
////    $result = Db::query(&#39;select * from banner_item where banner_id=?&#39;,[$id]);
////    return $result;
//    //2.使用查询构建器
//    /*
//     * 链式查询Db::table(&#39;banner_item&#39;)->where(&#39;banner_id&#39;,&#39;=&#39;,$id) 返回查询对象,->select();返回查询结果,
//     * 除了select操作还有 find(返回一条数据) update delete insert
//     * 对应的where 也分三种,1.表达式where(&#39;字段名&#39;,&#39;表达式&#39;,&#39;查询条件&#39;) 2.数组发 3.闭包。
//     */
//
//    // 2.1 表达式法
////    $result = Db::table(&#39;banner_item&#39;)
////      ->where(&#39;banner_id&#39;,&#39;=&#39;,$id)
////      ->select();
////    return $result;
//    //2.2 闭包法
//    $result = Db::table(&#39;banner_item&#39;)
//      ->where(function ($query) use($id){
//        $query->where(&#39;banner_id&#39;,&#39;=&#39;,$id);
//
//      })
//      ->select();
//    return $result;
//
//
//
//
//
//  }
}

controller:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/7
 * Time: 下午1:49
 */
namespace app\api\controller\v1;
use app\api\validate\IDMustBePositiveInt;
use app\lib\exception\BannerMissException;
use app\api\model\Banner as BannerModel;
class Banner
{
  public function getBanner($id){
     //调用验证器
    (new IDMustBePositiveInt())->goCheck();
//    $banner = BannerModel::getBannerByID($id);
    $banner = BannerModel::get($id);
    if(!$banner){
      throw new BannerMissException();
    }
    return $banner;
  }
}

Related recommendations:

thinkPHP query method summary

About thinkphp query and paging issues

The above is the detailed content of ThinkPHP5 method of querying data and processing results. For more information, please follow other related articles on the PHP Chinese website!