Reading Time: 7 minutes

3,363 Views

A procedure (often called a stored procedure) is a collection of pre-compiled SQL statements stored inside the database. It is a subroutine or a subprogram in the regular computing language.

Inside this article we will see the concept i.e PHP MySQLi How To Call Stored Procedure. This article will be very interesting to know and learn. We will see the complete guide of mysql stored procedure creation and calling from a PHP MySQLi application.

A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Next,

Table: items

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(120) NOT NULL,
 `description` text NOT NULL,
 `price` int(5) NOT NULL,
 `category_id` int(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Also, consider few rows inside it so that we can see stored procedure concept.

Create MySQL Stored Procedure (By PhpMyAdmin Interface)

Open MySQL database i.e php_applications

In this database we have table as items. In this table we have few rows. By the help of procedure, we will read all items as well as any specific item.

We will create stored procedure by using phpmyadmin manual interface option to add routines.

We will create two MySQL stored procedure here for this article.

Read all items from table
Read a Single Item from Table

Read All Items From Table

Read a Single Item From Table

Successfully, we have created two mysql stored procedures in database.

Create MySQL Stored Procedure (By Code)

We have the second option to create stored procedure by using MySQL code.

To get all items,

DELIMITER //

CREATE PROCEDURE getAllItems()
BEGIN
	SELECT *  FROM items;
END //

DELIMITER ;

To get single item detail

DELIMITER //

CREATE PROCEDURE getSingleItemDetail(
	IN item_id INT(5)
)
BEGIN
	SELECT * 
 	FROM items
	WHERE id = item_id;
END //

DELIMITER ;

You can choose any of the above process to create mysqli stored procedures. Either you can follow phpmyadmin interface of via code.

Create PHP Application

Create a folder named as php-sp inside your localhost directory. Inside this application folder create a file index.php.

Program To Read All Items

Open index.php and write this code into it.

<?php
// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$routineSql = "CALL getAllItems()";

$stmt = $conn->prepare($routineSql);
$stmt->execute();

$result = $stmt->get_result(); // get the mysqli result

$items = $result->fetch_all(MYSQLI_ASSOC); // fetch data 

echo "<pre>";
print_r($items);

Output

Program To Read Single Item

Open index.php and write this code into it.

<?php
// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$itemId = 1;

$routineSql = "CALL getSingleItemDetail(" . $itemId . ")";

$stmt = $conn->prepare($routineSql);
$stmt->execute();

$result = $stmt->get_result(); // get the mysqli result

$items = $result->fetch_assoc(); // fetch data 

echo "<pre>";
print_r($items);

Output

We hope this article helped you to learn PHP MySQLi How To Call Stored Procedure Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.