How to use MySQL stored procedures in PHP?

To use MySQL stored procedures in PHP: Connect to a MySQL database using PDO or the MySQLi extension. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

#How to use MySQL stored procedures in PHP?

Introduction

MySQL stored procedure is a precompiled SQL statement group that can be used to perform complex data operations. In PHP, we can interact with MySQL stored procedures using PDO extension or MySQLi extension.

Use PDO extension

<?php
// 打开 PDO 连接
$conn = new PDO('mysql:host=localhost;dbname=database_name;charset=utf8', 
               'username', 'password');

// 设置存储过程的名称
$procedure_name = 'my_stored_procedure';

// 准备调用存储过程的语句
$stmt = $conn->prepare("CALL $procedure_name()");

// 执行存储过程
$stmt->execute();

// 处理结果集（如果存储过程返回了结果集）
$result = $stmt->fetchAll();

// 关闭连接
$conn = null;
?>

Use MySQLi extension

<?php
// 打开 MySQLi 连接
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

// 设置存储过程的名称
$procedure_name = 'my_stored_procedure';

// 查询并执行存储过程
$result = $mysqli->query("CALL $procedure_name()");

// 处理结果集（如果存储过程返回了结果集）
while ($row = $result->fetch_assoc()) {
    // do something with the row
}

// 关闭连接
$mysqli->close();
?>

Practical case

Suppose we have a stored procedure get_customer_by_id, which receives a parameter customer_id and returns customer information. We can call this stored procedure using PHP using the following code snippet:

// 用 PHP 调用 get_customer_by_id 存储过程
$customer_id = 123;
$result = $mysqli->query("CALL get_customer_by_id($customer_id)");
$customer = $result->fetch_assoc();

We can then access the $customer array to get the details of a specified customer.

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