How to call mysql field in thinkphp

1. Create database tables and data

Before performing database operations, we need to create the database and corresponding data tables. We assume that we need to develop a student management system, in which a database table named student needs to be established to store basic information about students. The table contains the following fields:

id: primary key, auto-increment.

name: student name, varchar type, length 20.

age: student age, int type.

sex: Student gender, varchar type, length 2.

t_score: CET-4 test score, int type.

total_score: total student score, int type.

We can use the following SQL statement to create the data table:

CREATE TABLE 
student
 (
  
id
 int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  
name
 varchar(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
  
age
 int(11) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  
sex
 varchar(2) NOT NULL DEFAULT '' COMMENT '学生性别',
  
t_score
 int(11) NOT NULL DEFAULT '0' COMMENT 'CET-4考试成绩',
  
total_score
 int(11) NOT NULL DEFAULT '0' COMMENT '学生成绩总和',
  PRIMARY KEY (
id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

We randomly insert some data for subsequent testing:

INSERT INTO 
student
 (
name
, 
age
, 
sex
, 
t_score
, 
total_score
) VALUES ('张三', 20, '男', 550, 750), ('李四', 19, '女', 530, 700), ('王五', 22, '男', 610, 820), ('赵六', 23, '女', 590, 780), ('钱七', 21, '男', 500, 730);

2. In ThinkPHP Calling MySQL fields in

We can use the methods provided by the Db class to add, delete, modify, query, etc. to the database in ThinkPHP. When performing query operations, we need to call MySQL fields. The following lists some common calling methods:

1. Use array method to call directly:

We can use array method to directly call MySQL fields. Querying the id, name, age and other fields in the student table can be achieved in the following ways:

$studentList = Db::name('student')->field(['id', 'name' , 'age'])->select();

$field parameter is optional. Not passing this parameter means querying all fields.

2. Use string method to call:

The second method is to directly use string method to call the MySQL field, for example:

//Query id, name and age Field
$studentList = Db::name('student')->field('id, name, age')->select();

//Query t_score, total_score and total Sub-field (total score is the result of adding t_score and total_score)
$studentList = Db::name('student')->field('t_score, total_score, (t_score total_score) as score')-> select();

When calling MySQL fields using string methods, we can use as to alias a field (alias).

3. Call using model method:

When calling using model method, we need to define the model class first. A model class named Student can be defined according to the structure of the student table

namespace app\common\model;

use think\Model;

class Student extends Model
{

//定义表名和主键
protected $table = 'student';
protected $pk = 'id';

}

Next, we can use the Student model class to directly operate the query:

//Get all student list information
$studentList = Student ::field(['id', 'name', 'age'])->select();

//Get student name and total score
$studentList = Student::field( 'name, (t_score total_score) as score')->select();

Calling MySQL fields through models can make our code more concise and elegant, and can effectively prevent SQL injection problems.

3. Perform MySQL field alias operations in ThinkPHP

When executing SQL query statements, sometimes we need to use column aliases as the identifier of the returned results. For example, if we want to display the student's name and the number of other students with higher scores than the student in the query results, we can add an alias:

$studentList = Db::name('student')->field('name, (SELECT count(*) FROM student as b WHERE b.total_score>s.total_score) as rank')->alias('s')->select();

The above code can be obtained by subquery and alias method The corresponding SQL statement for the person's name and ranking is:

SELECT name, (SELECT count(*) FROM student as b WHERE b.total_score>s.total_score) as rank FROM student s

In ThinkPHP, by using the alias method, we can set a specified alias for the MySQL field to achieve more efficient query operations.

The above is the detailed content of How to call mysql field in thinkphp. For more information, please follow other related articles on the PHP Chinese website!