How to call a MySQL stored procedure from within PHP code?

Steps to Call MySQL Stored Procedures

Calling MySQL stored procedures typically involves the following steps:

1. Establish a database connection: First, use PHP functions or libraries such as mysqli or PDO to connect to the MySQL database.
2. Prepare the SQL statement: Prepare the SQL statement to call the stored procedure using PHP.
3. Execute the stored procedure: Execute the stored procedure call via the PHP database connection.
4. Process the returned results: Handle the results returned by the stored procedure, which may include output parameters, return values, or result sets.
5. Close the database connection: Close the database connection after the operation to release resources.

Example Code

Assume we have a stored procedure called GetCustomerByID that accepts a parameter customer_id and returns the customer's detailed information.

Using the mysqli Extension

php
<?php
// Database connection configuration
$host = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($host, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare the SQL statement for calling the stored procedure
$sql = "CALL GetCustomerByID(?)";

// Prepare the statement
$stmt = $conn->prepare($sql);

// Bind parameters
$customer_id = 101;
$stmt->bind_param("i", $customer_id);

// Execute the query
$stmt->execute();

// Bind result variables
$stmt->bind_result($id, $name, $email);

// Fetch values
while ($stmt->fetch()) {
    echo "ID: $id, Name: $name, Email: $email\n";
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

Using the PDO Extension

php
<?php
// Database connection configuration
$host = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create PDO instance
$dsn = "mysql:host=$host;dbname=$dbname";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare the SQL statement for calling the stored procedure
    $sql = "CALL GetCustomerByID(?)";

    // Prepare the statement
    $stmt = $pdo->prepare($sql);

    // Bind parameters
    $customer_id = 101;
    $stmt->bindParam(1, $customer_id, PDO::PARAM_INT);

    // Execute the statement
    $stmt->execute();

    // Fetch results
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($result as $row) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "\n";
    }
} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}

// Close connection
$pdo = null;
?>

Summary

In this example, we demonstrate how to call MySQL stored procedures from PHP code using the mysqli and PDO extensions. The choice of method depends on your specific requirements and personal preference. Ensure proper handling of security and exceptions in production environments to maintain application stability.