thinkphp5 calls stored procedure

When developing web applications, it is often necessary to call stored procedures to perform some complex database operations. ThinkPHP5, as a popular PHP framework, provides convenient and fast solutions for database operations. This article will introduce how to call stored procedures in ThinkPHP5.

1. Create a stored procedure

First, we need to create a stored procedure in the database. Taking MySQL as an example, assume that we have created the following stored procedure:

DELIMITER $$
CREATE PROCEDURE user_login(IN `username` varchar(50), IN `password` varchar(50), OUT `result` varchar(20))
BEGIN
    SELECT COUNT(*) INTO result FROM user WHERE `username`=username AND `password`=password;
END $$
DELIMITER ;

This stored procedure is used to verify user login information and accepts two input parameters: user name and password, and one output parameter: login result .

2. Call in the model

Next, we need to call the stored procedure in the corresponding model. For existing models, you can add the following code to the corresponding method:

$result = Db::query("CALL user_login('".$username."','".$password."',@result);");

The Db::query method is used here to execute the stored procedure. The first parameter is the SQL statement of the stored procedure. Note that the parameter value is wrapped in double quotes. The second parameter is a reference variable used to obtain the output of the stored procedure. Before that, we need to define a variable to save the output result:

$result = '';

Finally, we need to add the following code before executing the stored procedure to ensure that the output parameters can be obtained correctly:

Db::query("SELECT @result AS result;");

Complete The code is as follows:

public function login($username, $password)
{
    $result = '';

    Db::query("SELECT @result := '';");

    $result = Db::query("CALL user_login('".$username."','".$password."',@result);");

    Db::query("SELECT @result AS result;");

    return intval($result[0]['result']) === 1;
}

Among them, the intval function is used to convert a string into an integer.

For the new model, you can follow the steps below:

1. Create a new model class, such as UserModel, and add the following code:

Call the model method in the controller, for example: 
public function login()
{
    $username = input('post.username');
    $password = input('post.password');

    if(UserModel::login($username, $password)){
        return json(['code' => 0, 'message' => '登录成功']);
    } else {
        return json(['code' => -1, 'message' => '登录失败']);
    }
}

Run the program, if everything goes well, you should be able to log in successfully. 
Summary

Through the introduction of this article, we have learned how to call stored procedures in ThinkPHP5. It should be noted that before executing the stored procedure, you must set the output parameters and query the results after execution. At the same time, when using the Db::query method, you need to pay attention to how to pass parameters and how to obtain output parameters. By rationally using stored procedures, we can perform complex database operations more conveniently during the development process. 
The above is the detailed content of thinkphp5 calls stored procedure. For more information, please follow other related articles on the PHP Chinese website!