We can easily call the stored procedure from the MySQL workbench or MySQL CLI. But have you ever tried to call it from PHP and failed? Don’t worry now because, in this tutorial, we will learn to call a stored procedure from a PHP program. We will see two examples of different types so that you will learn to call stored procedures in different ways. So, let’s get started!
Introduction to Stored Procedures in PHP
In MySQL stored procedure, you can send values to the procedure body using the IN and return any values using the OUT parameter. Creating such stored procedures is an easy task if you write SQL queries on MySQL Workbench or MySQL CLI. However, you may find it tricky when you want to call these stored procedures from a PHP program.
Let me tell you that calling a stored procedure from a PHP program is as simple as fetching the result using the SELECT statement.
We will get two examples below.
The first example will demonstrate how to fetch a result set that contains multiple rows using the stored procedure in the PHP program. On the other hand, the second example will demonstrate how to call a stored procedure with OUT/INOUT parameters in the PHP program.
Examples of Calling Stored Procedures in PHP
To demonstrate the following examples, we will use object-oriented MySQLi instead of PDO or procedural-based MySQLi. Also, the following table description and data will be used to create stored procedures.
Example 1. Calling a stored procedure that returns a result set
This is a simple example in which the stored procedure will return multiple rows and we will display them on the webpage using PHP.
To accomplish the task, we first call the procedure from the PHP program. Then we fetch the result and iterate the result set using the loop to get individual rows of data.
Let’s see the live demo now.
First, we will create a stored procedure using MySQL CLI to fetch all data from the table.
DELIMITER //
CREATE PROCEDURE fetchData()
BEGIN
SELECT * FROM orders;
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
As you can see, we have used the SELECT statement to fetch the table data. Let’s write a PHP program now to call the procedure and display the data on the webpage.
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP stored procedure</title>
<style>
table {
border-collapse: collapse;
}
td{
padding: 5px;
}
</style>
</head>
<body>
<h3>Fetch Result Set From the Stored Procedure</h3>
<?php
$conn = new mysqli("localhost", "root", "", "journaldev");
$query = "CALL fetchdata();";
$result = $conn->query($query);
?>
<table border="1">
<tr>
<td>id</td>
<td>orderID</td>
<td>prodID</td>
<td>ProdName</td>
<td>orderDate</td>
<td>shipDate</td>
<td>custID</td>
<td>status</td>
</tr>
<?php
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $row['id'] ?></td>
<td><?php echo $row['order_id'] ?></td>
<td><?php echo $row['prod_id'] ?></td>
<td><?php echo $row['prod_name'] ?></td>
<td><?php echo $row['order_date'] ?></td>
<td><?php echo $row['ship_date'] ?></td>
<td><?php echo $row['customer_id'] ?></td>
<td><?php echo $row['status'] ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>Code language: PHP (php)Here, we have done the following things-
- First, we established the connection with the database and created an object.
- Second, we wrote the “call” query and assigned it to the variable.
- Third, we have executed the query using the conn->query statement.
- Forth, we have used the fetch_assoc statement to fetch all the data from the previously executed query.
- The fetched data is in array format. So we use the WHILE loop to get individual row data.
- Finally, we display the data using the column name using the echo in PHP.
Let’s save the above code in example.php and open it in the web browser to check the output.
As you can see, we have received all data from the table on our webpage.
Example 2. Calling a stored procedure with IN and OUT Parameters
People find this example tricky because many of you don’t know how to use the OUT parameter while calling the stored procedure in PHP. It’s as easy as the previous example. As you know, we use session variables to get the returned value of the procedure. We will follow the same steps in the PHP program also. Let’s see an example below.
But first, let’s create a stored procedure that returns a total number of orders being placed for a particular product by using the product_id.
DELIMITER //
CREATE PROCEDURE findOrders(IN pid INT, OUT totOrders INT)
BEGIN
SELECT COUNT(*) INTO totOrders FROM orders WHERE prod_id=pid;
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
In the above procedure, we have used the COUNT function to count the number of orders and copied it into the totOrders variable using the SELECT INTO statement. To find the orders by the produt_id, we have used the WHERE clause.
Let’s write a PHP program now to display the result on the webpage.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP stored procedure</title>
</head>
<body>
</body>
<h3>Using OUT/INOUT Parameters in the stored procedure</h3>
<?php
$pid = 121;
$conn = new mysqli("localhost", "root", "", "journaldev");
$query = "CALL findOrders($pid,@orders);";
$conn->query($query);
$result = $conn->query("SELECT @orders as TotalOrders;");
$row = $result->fetch_assoc();
echo "<h3>Total orders of product id $pid are = " . $row['TotalOrders'] . "</h3>";
?>
</html>Code language: PHP (php)Here, we have done the following things-
- First, we have established the connection and created an object.
- Second, we wrote the “call” query and assigned it to the variable. We have specified a session variable using the @ sign.
- Third, we have executed the query using the conn->query statement.
- Forth, we have used the SELECT statement to get the result which is stored in the session variable.
- Fifth, we have used the fetch_assoc statement to fetch data using PHP.
- Finally, we have displayed the result using the echo.
Let’s save the above code in the file example.php and open it in the web browser.
As you can see, we have received the expected output.
Summary
In this tutorial, we learned-
- Introduction to stored procedure in the PHP program.
- Display the result set by calling a stored procedure in the PHP program.
- Use the OUT parameter in the stored procedure and call it using the PHP program.
I hope you find this article helpful. We highly recommend you try some examples using other methods in PHP such as PDO and procedural-oriented. However, you are free to use any method at your convenience.