How to Call MySQL Stored Procedures from PHP?

Calling MySQL Stored Procedures from PHP

When working with a database, stored procedures offer a structured and encapsulated way to execute repetitive database operations. If you want to integrate this functionality into your PHP application, you can leverage the mysqli extension, which provides support for calling stored procedures in MySQL databases.

Example:

Consider the following scenario:

• MySQL client version: 4.1.11
• MySQL server version: 5.0.45

You have a stored procedure named getTreeNodeName that takes an nid parameter and returns the corresponding node name. Below is the PHP code you can use to invoke this stored procedure:

<?php

// Enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Connect to the MySQL database
$connection = mysqli_connect("hostname", "user", "password", "db", "port");

// Create a prepared statement for the stored procedure
$stmt = mysqli_prepare($connection, "CALL getTreeNodeName(?)");

// Bind the parameter to the statement
mysqli_stmt_bind_param($stmt, "i", $nid);

// Execute the prepared statement
mysqli_stmt_execute($stmt);

// Bind the result to variables
mysqli_stmt_bind_result($stmt, $nodeName);

// Fetch the result
mysqli_stmt_fetch($stmt);

// Print the result
echo $nodeName;

// Close the prepared statement and connection
mysqli_stmt_close($stmt);
mysqli_close($connection);

?>

Note:

If you encounter issues using the mysql_connect, mysql_query, and mysql_fetch_array functions, consider using the mysqli extension instead, which offers improved security and performance.

The above is the detailed content of How to Call MySQL Stored Procedures from PHP?. For more information, please follow other related articles on the PHP Chinese website!