php-mysqlHow to call a stored procedure in MySQL using PHP?
Calling a stored procedure in MySQL using PHP is a simple process. The following example code block shows how to call a stored procedure named myProcedure
:
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* call the stored procedure */
if ($mysqli->multi_query("CALL myProcedure()")) {
do {
/* store first result set */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
$result->free();
}
/* print divider */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
}
/* close connection */
$mysqli->close();
The output of the example code above would be the result of the stored procedure myProcedure
:
Result of myProcedure
-----------------
More results of myProcedure
Code explanation
-
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
- This line creates a new MySQLi object with the given parameters. -
if (mysqli_connect_errno()) {
- This line checks if there is an error in the connection. -
if ($mysqli->multi_query("CALL myProcedure()")) {
- This line calls the stored proceduremyProcedure
. -
if ($result = $mysqli->store_result()) {
- This line stores the result of the stored procedure. -
while ($row = $result->fetch_row()) {
- This line fetches the result row by row. -
printf("%s\n", $row[0]);
- This line prints the result of the stored procedure. -
$result->free();
- This line frees the result of the stored procedure. -
if ($mysqli->more_results()) {
- This line checks if there are more results. -
$mysqli->close();
- This line closes the connection.
Helpful links
More of Php Mysql
- How to create pagination in MySQL using PHP?
- How to write an update query in MySQL using PHP?
- How to join tables with PHP and MySQL?
- How to output XML from MySQL using PHP?
- How to count the number of resulting rows in a MySQL database using PHP?
- How to use a variable in a MySQL query using PHP?
- How to use utf8mb4_unicode_ci in MySQL with PHP?
- How to create an SSL connection to MySQL using PHP?
- How to check the result of an insert in PHP and MySQL?
- How to use a MySQL union in PHP?
See more codes...