Calling stored procedures in PHP (PDO)

To call a stored procedure from a PHP application, you execute an SQL CALL statement. The procedure that you call can include input parameters (IN), output parameters (OUT), and input and output parameters (INOUT).

To call a stored procedure:

1. Call the PDO::prepare method to prepare a CALL statement with parameter markers that represent the OUT and INOUT parameters.
2. For each parameter marker in the CALL statement, call the PDOStatement::bindParam method to bind each parameter marker to the name of the PHP variable that will hold the output value of the parameter after the CALL statement has been issued. For INOUT parameters, the value of the PHP variable is passed as the input value of the parameter when the CALL statement is issued.
   1. Set the third parameter, data_type, to one of the PDO::PARAM_* constants that specifies the type of data being bound:

      PDO::PARAM_NULL

      Represents the SQL NULL data type.

      PDO::PARAM_INT

      Represents SQL integer types.

      PDO::PARAM_LOB

      Represents SQL large object types.

      PDO::PARAM_STR

      Represents SQL character data types.

      For an INOUT parameter, use the bitwise OR operator to append PDO::PARAM_INPUT_OUTPUT to the type of data being bound.
   2. Set the fourth parameter, length, to the maximum expected length of the output value.
3. Call the PDOStatement::execute method, passing the prepared statement as an argument.

   For more information about the PDO API, see http://php.net/manual/en/book.pdo.php.

$sql = 'CALL match_animal(?, ?)';
$stmt = $conn->prepare($sql);

$second_name = "Rickety Ride";
$weight = 0;

$stmt->bindParam(1, $second_name, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 32);
$stmt->bindParam(2, $weight, PDO::PARAM_INT, 10);

print "Values of bound parameters _before_ CALL:\n";
print "  1: {$second_name} 2: {$weight}\n";

$stmt->execute();

print "Values of bound parameters _after_ CALL:\n";
print "  1: {$second_name} 2: {$weight}\n";