Summary of ThinkPHP5 Query Data and Processing Results

In this paper, the method of querying data and processing results in ThinkPHP5 is described with examples. Share it for your reference, as follows:

There are some problems when dealing with database query results, and several query methods and result processing used are recorded.

1. Query a 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 record, a field

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

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

3. Query a field of multiple records and process the result, which 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 form 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];
  }
}

Then summarize the three database query methods of TP5 under 1

Mode 1: Native sql query

Code example:

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

Way 2: Use the query builder

Code example:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/8
 * Time:  Afternoon 2:15
 */
namespace app\api\model;
use think\Db;
use think\Exception;
class Banner
{
  public static function getBannerByID($id){
    //1. Use native sql
//    $result = Db::query('select * from banner_item where banner_id=?',[$id]);
//    return $result;
    //2. Using the Query Builder 
    /*
     *  Chain query Db::table('banner_item')->where('banner_id','=',$id)  Returns the query object ,->select(); Return query results ,
     *  Except for select Operations also include  find( Return 1 Bar data ) update delete insert
     *  Corresponding where  Yefen 3 Species ,1. Expression where(' Field name ',' Expression ',' Query criteria ') 2. Array sending  3. Closure. 
     */
    // 2.1  Expression method 
//    $result = Db::table('banner_item')
//      ->where('banner_id','=',$id)
//      ->select();
//    return $result;
    //2.2  Closure method 
    $result = Db::table('banner_item')
      ->where(function ($query) use($id){
        $query->where('banner_id','=',$id);
      })
      ->select();
    return $result;
  }
}

Mode 3: ORM (Object Relation Mapping) Object Relational Mapping

The main difference of using ORM to query the database is that the controller writes the inherited think\ model class of the model, and then the controller can use the default method of model to get the data instead of writing a special method in the model

Code example:

model:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/8
 * Time:  Afternoon 2:15
 */
namespace app\api\model;
use think\Db;
use think\Model;
class Banner extends Model
{
//  public static function getBannerByID($id){
//    //1. Use native sql
////    $result = Db::query('select * from banner_item where banner_id=?',[$id]);
////    return $result;
//    //2. Using the Query Builder 
//    /*
//     *  Chain query Db::table('banner_item')->where('banner_id','=',$id)  Returns the query object ,->select(); Return query results ,
//     *  Except for select Operations also include  find( Return 1 Bar data ) update delete insert
//     *  Corresponding where  Yefen 3 Species ,1. Expression where(' Field name ',' Expression ',' Query criteria ') 2. Array sending  3. Closure. 
//     */
//
//    // 2.1  Expression method 
////    $result = Db::table('banner_item')
////      ->where('banner_id','=',$id)
////      ->select();
////    return $result;
//    //2.2  Closure method 
//    $result = Db::table('banner_item')
//      ->where(function ($query) use($id){
//        $query->where('banner_id','=',$id);
//
//      })
//      ->select();
//    return $result;
//
//
//
//
//
//  }
}

controller:

<?php
/**
 * Created by PhpStorm.
 * User: chenzhitao
 * Date: 2017/5/7
 * Time:  Afternoon 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){
     // Invoke the validator 
    (new IDMustBePositiveInt())->goCheck();
//    $banner = BannerModel::getBannerByID($id);
    $banner = BannerModel::get($id);
    if(!$banner){
      throw new BannerMissException();
    }
    return $banner;
  }
}

For more readers interested in thinkPHP related contents, please check the topics of this site: "ThinkPHP Introduction Tutorial", "thinkPHP Template Operation Skills Summary", "ThinkPHP Common Methods Summary", "codeigniter Introduction Tutorial", "CI (CodeIgniter) Framework Advanced Tutorial", "Zend FrameWork Framework Introduction Tutorial" and "PHP Template Technology Summary".

I hope this article is helpful to the PHP programming based on ThinkPHP framework.