In this tutorial, we will be looking at how we can return multiple values from the stored procedure. After one practical example, we will see how can we implement the same example in PHP. Let’s dive into it.
Also read: Create MySQL Stored Procedure [With Examples]
Before We Start
MySQL stored function can return only one value. So, when you want your program to return multiple values, the stored procedure comes into action.
The stored procedure supports three types of parameters, IN, OUT and INOUT out of which OUT and INOUT are used for returning the values from the stored procedure to the calling program.
If you are not familiar with different types of parameters in the stored procedure, we recommend you to read our detailed guide on MySQL stored procedure parameters (link to the previous article).
Example to Return Multiple Values From Stored Procedure
To demonstrate the examples, we will use the table of the following description and data.
Now, we will create a stored procedure that returns a total number of orders that are shipped, delivered and cancelled.
DELIMITER //
CREATE PROCEDURE orders_summary(OUT shipped INT,OUT delivered INT, OUT canceled INT)
BEGIN
SELECT COUNT(*) INTO shipped FROM orders WHERE status="shipped";
SELECT COUNT(*) INTO delivered FROM orders WHERE status="delivered";
SELECT COUNT(*) INTO canceled FROM orders WHERE status="canceled";
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)In the above example, we have declared three OUT parameters to hold the values of shipped, delivered and cancelled orders.
In the procedure body, we have written three SELECT statements with the COUNT function to count the number of orders on the condition of their status using the WHERE clause.
So, we are returning multiple values from a single stored procedure. Let’s test it now.
CALL orders_summary(@shipped, @delivered, @canceled);
SELECT @shipped, @delivered, @canceled;Code language: SQL (Structured Query Language) (sql)If you check the table data, there are two delivered orders, two cancelled orders and one is shipped order. We should get the result this way.
As you can see, we received the expected result which means our procedure that returns multiple values works perfectly!
Return Multiple Values From Stored Procedure in PHP
The above example was demonstrated using MySQL CLI. Now we will perform the same example in PHP.
We already have created the stored procedure above. We will use the same procedure in the below example inside the PHP.
In the below example, we have used prepared statements. MySQLi doesn’t provide any native support to the output parameters so you must output the returned values of the stored procedure into user variables and then fetch the values using the SELECT statement.
<?php
$conn = mysqli_connect("localhost", "root", "", "journaldev");
$call = mysqli_prepare($conn, 'CALL orders_summary(@shipped, @delivered, @canceled)');
mysqli_stmt_execute($call);
$select = mysqli_query($conn, 'SELECT @shipped, @delivered, @canceled');
$result = mysqli_fetch_assoc($select);
echo "Total shipped orders are = ".$result['@shipped'] . '<br>';
echo "Total delivered orders are = ".$result['@delivered'] . '<br>';
echo "Total canceled orders are = ".$result['@canceled'] . '<br>';
?>Code language: PHP (php)Here, we have used the database credentials to connect to the database. Then we used the prepared statement to call the stored procedure. Then we used the SELECT statement to fetch values from the user variables.
Let’s save the code in the “ret_multiple.php” file and run it in the browser.
We get the following output in the browser.
As you can see, we have received the expected output.
Conclusion
In this tutorial, We learned how to return multiple values from a stored procedure. Along with it, we saw an example in which we called the stored procedure, which returned multiple values in PHP and displayed in the browser. I hope this tutorial was helpful to you. See you in the next tutorial!