Stored procedures are database routines called using driver-specific SQL such as CALL. PDO supports prepared CALL statements on drivers that implement them.
What It Does
Use a dedicated limited-permission database account, not the root account. Drain each result set when a procedure returns several tables.
Requirements and Compatibility
Use a supported PHP runtime and install any third-party tool or extension explicitly shown in the examples. Commands should be run from the project root unless stated otherwise.
Syntax, Inputs and Results
The examples separate configuration, the smallest working operation and a realistic follow-up task. Adapt file paths and dependency versions to your own project.
Example 1: Basic Use
Use a dedicated limited-permission database account, not the root account.
<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=app;charset=utf8mb4', 'app_user', getenv('DB_PASSWORD'));
$stmt = $pdo->prepare('CALL find_orders_by_customer(?)');
$stmt->execute([23]);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) print_r($row);
$stmt->closeCursor();Example 2: Another Case
Drain each result set when a procedure returns several tables.
<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=app;charset=utf8mb4', 'app_user', getenv('DB_PASSWORD'));
$stmt = $pdo->prepare('CALL get_monthly_report(?)');
$stmt->execute(['2026-10']);
do { foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) print_r($row); }
while ($stmt->nextRowset());Practical Example
Distinct parameter names prevent ambiguous column references inside a stored routine.
CREATE PROCEDURE find_orders_by_customer(IN p_customer_id INT)
SELECT id, total FROM orders WHERE orders.customer_id = p_customer_id;For production, apply the compatibility and edge-case limits described below.
Important Behavior and Edge Cases
A stored procedure may return multiple result sets; use nextRowset() or closeCursor() before issuing another query on the connection.
Comparison with Related Functions and Tools
| Subject | Key difference |
|---|---|
| Prepared statements vs SQL concatenation | Prepared statements bind data values; concatenation can turn unchecked input into SQL syntax. |
Common Beginner Mistakes
| Mistake | Correct approach |
|---|---|
| Interpolating user values into database SQL | Use named or positional placeholders and handle database errors. |
| Skipping the prerequisite of this specific workflow | Install or configure the named tool and confirm the example can actually run. |
| Ignoring edge conditions | A stored procedure may return multiple result sets; use nextRowset() or closeCursor() before issuing another query on the connection. |
Key Takeaways
- Stored procedures are database routines called using driver-specific SQL such as CALL. PDO supports prepared CALL statements on drivers that implement them.
- A stored procedure may return multiple result sets; use nextRowset() or closeCursor() before issuing another query on the connection.
- Keep application configuration, source code and validation tests under version control.
Use these examples as a starting point, then validate the result with the actual PHP build, dependencies and inputs used by your application.