PHP MySQL: Call MySQL Stored Procedures

PHP MySQL: Call MySQL Stored Procedures

Calling MySQL-stored procedures from PHP is a powerful way to encapsulate complex database logic in reusable database-side code. You can use PHP’s mysqli or PDO extension to execute stored procedures.

Steps to Call Stored Procedures in PHP

1. Create a Stored Procedure in MySQL
2. Connect to the MySQL Database in PHP
3. Call the Stored Procedure Using PHP
4. Handle Input and Output Parameters
5. Fetch Results if Applicable

1. Creating a Stored Procedure in MySQL

Below is an example of a stored procedure named GetUserByID:

DELIMITER $$

CREATE PROCEDURE GetUserByID(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END$$

DELIMITER ;

• IN Parameter: Takes input from the caller.
• SELECT Statement: Returns data from the users table.

2. Connecting to the Database

First, establish a connection to the MySQL database:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

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

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

3. Calling the Stored Procedure

Call Procedure with IN Parameters

<?php
// Prepare the stored procedure call
$user_id = 1;
$sql = "CALL GetUserByID(?)";

$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $user_id);

// Execute the query
if ($stmt->execute()) {
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "Error: " . $stmt->error;
}

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

4. Fetching Results from the Procedure

For stored procedures that return multiple rows, you can fetch the result set using get_result():

<?php
$sql = "CALL GetAllUsers()"; // Example procedure with no parameters

if ($result = $conn->query($sql)) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
    $result->close();
} else {
    echo "Error: " . $conn->error;
}
$conn->close();
?>

5. Handling Output Parameters

Stored procedures with output parameters can be handled using prepared statements.

Example Stored Procedure with an OUT Parameter

DELIMITER $$

CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END$$

DELIMITER ;

PHP Code to Call the Procedure

<?php
$sql = "CALL GetUserCount(@count)";
$conn->query($sql);

$result = $conn->query("SELECT @count AS user_count");
$row = $result->fetch_assoc();

echo "Total Users: " . $row["user_count"];

$conn->close();
?>

6. Handling Multiple Result Sets

If a stored procedure returns multiple result sets, use next_result() to iterate over them.

<?php
$sql = "CALL GetMultipleResults()"; // Example procedure returning multiple result sets

if ($conn->multi_query($sql)) {
    do {
        if ($result = $conn->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo "Data: " . $row["column_name"] . "<br>";
            }
            $result->free();
        }
    } while ($conn->next_result());
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Error Handling

Always handle errors gracefully when working with stored procedures:

if ($conn->error) {
    echo "MySQL Error: " . $conn->error;
}

Complete Example

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

// Connect to MySQL
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Call stored procedure
$user_id = 2;
$sql = "CALL GetUserByID(?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $user_id);

if ($stmt->execute()) {
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "Error: " . $stmt->error;
}

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

---

Best Practices

1. Validate Input: Always validate and sanitize inputs to prevent SQL injection.
2. Error Logging: Log errors to troubleshoot issues with stored procedures.
3. Use Prepared Statements: Avoid directly embedding variables in SQL strings.
4. Optimize Procedures: Keep stored procedures optimized for better performance.

Let me know if you need help with specific examples or troubleshooting!