How to call stored procedure in PHP with MySQLi

In this tutorial, we are going to see how to call stored procedure in PHP with MySQLi. Stored procedures can help improve web applications and reduce database query traffic.

Suppose we have the following stored procedure, which finds the price of the most expensive product in the “Products” table:

DELIMITER $

CREATE PROCEDURE `max_price`(out max_price decimal)
BEGIN
    SELECT MAX(Price) into max_price FROM products;
END

 

 

How to call stored procedure in PHP with MySQLi

With PDO, calling a stored procedure is easy. The following PHP code call the above function max_price():

<?php

$host = 'localhost'; 
$db = 'test';
$username = 'root';
$password = ' ';
$dsn = "$mysql:host=$host;dbname=$db";

$pdo = new PDO($dsn, $username, $password);

$q = $pdo->exec('call max_price(@out)');
$res = $pdo->query('select @out')->fetchAll();
print_r($res);

?>

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More