Detailed explanation of how to call mysql fields in thinkphp

ThinkPHP is an open source PHP framework. Its excellence is not only its code quality, but more importantly its easy use, flexible configuration, and powerful database operation functions. In ThinkPHP, how to call MySQL fields is an important skill that we need to master.

1. Create database tables and data

Before performing database operations, we need to create the database and corresponding data tables. Suppose we have a student management system and need to create a data table named student 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 'Auto-increment ID',
name varchar(20) NOT NULL DEFAULT '' COMMENT 'Student's name',
age int(11) NOT NULL DEFAULT '0' COMMENT 'Student's age',
sex varchar(2) NOT NULL DEFAULT '' COMMENT 'Student's gender',
t_score int(11) NOT NULL DEFAULT '0' COMMENT 'CET-4 test scores',
total_score int(11) NOT NULL DEFAULT '0' COMMENT 'Total student scores',
PRIMARY KEY (id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Student Information Table';

We randomly insert some data for subsequent testing:

INSERT INTO student (name, age, sex, t_score, total_score) VALUES ('张Three', 20, 'Male', 550, 750), ('李四', 19, 'Female', 530, 700), ('王五', 22, 'Male', 610, 820), (' Zhao Liu', 23, 'Female', 590, 780), ('Qian Qi', 21, 'Male', 500, 730);

2. Call MySQL fields in ThinkPHP

In ThinkPHP, we can add, delete, modify, query and other operations to the database through the methods provided by the Db class. 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. For example, if we want to query id, name, age and other fields from the student table, we can use the following method:

$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. For example, we can define a Student model class based on 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 performing SQL statement queries, we sometimes need to use some column aliases as return 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 get the name and ranking of the person through the subquery and alias method. The corresponding SQL statement is:

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

In ThinkPHP, we can add specific aliases to MySQL fields through the alias method to achieve more efficient query operations.

4. Summary

In this article, we explained three ways to call MySQL fields in ThinkPHP: array method call, string method call and model method call, and introduced adding Operation of MySQL field aliases. These methods have their own applicable scenarios and can be flexibly selected according to different needs. It can also be seen from this that ThinkPHP, as an excellent PHP framework, has very powerful and flexible database operation capabilities.

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