How to write database stored procedures in PHP

With the rapid development of web applications, the demand for databases is also increasing. PHP has become one of the most widely used languages ​​for web applications because it is very flexible and easy to use. Database stored procedures have become a very important tool for applications that need to efficiently process large amounts of data. By using stored procedures, developers can encapsulate complex SQL code into the database, allowing them to better manage the code and improve performance.

In this article, I will show you how to write and call database stored procedures using PHP. I will use a MySQL database for demonstration.

1. Create database and tables

First, we need to create a MySQL database and some tables to store data. Here, I will create a database named "test" and create two tables: users and orders, where the users table will contain user information, and the orders table will contain order information. The following is the SQL statement to create the user table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Similarly, the following is the SQL statement to create the order table:

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `quantity` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Create a stored procedure

In this step , we will create a simple stored procedure to insert user data into the users table. This stored procedure will receive three parameters: name, email and password, and insert a new user record.

The following is the PHP code to create this stored procedure:

$db = new mysqli('localhost', 'root', '', 'test');

if ($db->connect_error) {
    die('连接数据库失败: ' . $db->connect_error);
}

// 创建存储过程
$sql = "CREATE PROCEDURE `insert_user` (IN name VARCHAR(255), IN email VARCHAR(255), IN password VARCHAR(255))
BEGIN
    INSERT INTO `users` (`name`, `email`, `password`) VALUES (name, email, password);
END";
$db->query($sql);

echo "存储过程已创建";

In this example, we create a stored procedure named insert_user and define three input parameters name, email and password. Within the stored procedure, we simply insert these parameters into the users table. Now we can execute this stored procedure through the $db->query() method.

3. Call the stored procedure

The following PHP code will call the insert_user stored procedure just created and insert a new user record:

$db = new mysqli('localhost', 'root', '', 'test');

if ($db->connect_error) {
    die('连接数据库失败: ' . $db->connect_error);
}

// 调用存储过程
$name = 'Tom';
$email = 'tom@example.com';
$password = '123456';

$sql = "CALL `insert_user`('$name', '$email', '$password')";
$db->query($sql);

echo "用户已添加";

In this example, We first created a mysqli object to connect to the MySQL database. Then, we use the three variables "Tom", "tom@example.com" and "123456" as parameters to call the stored procedure that inserts the user record. Finally, we output a success message.

Through the above example, we have created and called a simple stored procedure. Of course, we can also use stored procedures to perform more complex tasks, such as data transformation, data cleaning, data aggregation, and report generation.

In actual applications, stored procedures can greatly improve database performance. When you want to perform a repetitive operation or need to process complex SQL statements, using stored procedures will make your code more concise and improve system performance and maintainability by encapsulating this logic into the database.

When writing stored procedures, you should pay attention to security and maintainability. When writing stored procedures, you should consider using parameterized queries to prevent SQL injection attacks and avoid using excessive code logic and unnecessary calculations.

In short, the PHP language has become one of the most widely used languages ​​for web applications and has become a key tool for processing large amounts of data. By using stored procedures, you can encapsulate complex SQL code into the database, improving system performance and maintainability.

The above is the detailed content of How to write database stored procedures in PHP. For more information, please follow other related articles on the PHP Chinese website!