Calling a Stored Procedure from PHP

Calling a stored procedure with PHP only possible after connecting PHP with Mysql.

Apart from conventional way of mysql connection . There are two ways to connect PHP with mysql
i)    The database independent PHP:: DB extenstion  which is also known as Mysqli extension
ii)    Recent PHP Data Objects (PDO) extension.

In our example we will use Mysqli. There are two section of this example.
i)    Mysql Procedure
ii)    Connection with PHP

Please use below code to get MySql Table Created

--
-- Table structure for table `employees`
--

CREATE TABLE IF NOT EXISTS `employees` (
`employee_id` int(11) NOT NULL,
`surname` varchar(100) NOT NULL,
`firstname` varchar(100) NOT NULL,
`department_id` int(11) NOT NULL,
`status` int(11) NOT NULL,
`flag` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`employee_id`, `surname`, `firstname`, `department_id`, `status`, `flag`) VALUES
(1, 'pandey', 'cs', 1, 1, 1),
(2, 'pandey', 'abhinav', 1, 1, 1);

Mysql Procedure example:

DELIMITER  $$
DROP PROCEDURE IF EXISTS employee_list $$
CREATE PROCEDURE employee_list (in_depth_in INT)
READS SQL DATA
BEGIN
SELECT employee_id,surname,firstname FROM employees WHERE department_id = in_depth_in;

END $$

Now we are coming to next part of example: PHP code to call MySql Procedures

<html>
<head>
<title>Employee Listing</title>
</head>
<body>
<h1>Employee Listing</h1>
<form method="post">
<p>Enter Department id: <input type="text" name="dept_id" size="4">
<input type="submit" name="submit" value="submit"></p>

</form>
<?php
$hostname="localhost";
$username="root";
$password="";
$database="test_mysql";
if(isset($_POST['dept_id']))
{
$dbh = new mysqli($hostname,$username,$password,$database);
/*check connection*/
if(mysqli_connect_error())
{
printf("Connect failed %sn",mysqli_connect+error());
exit();
}
$dept_id = $_POST['dept_id'];
if($result_set =$dbh->query("call employee_list(".$dept_id.")"))
{
print('<table border='1'  width="30%"><tr><td>Employee id</td><td>Firstname</td><td>Surname</td></tr>');
while($row = $result_set ->fetch_object())
{
printf('<tr><td>%s</td><td>%s</td><td>%s</td></tr>n',$row->employee_id,$row->firstname,$row->surname);
}
}
else
{
printf("<p>Error:%d(%s) %sn ",mysql_errno($dbh),mysqli_sqlstate($dbh),mysqli_error($dbh));
}
print("</table>");
$dbh->close();
}
?>
</body></html>

Now You are done: You can see output like this

Related posts:

The following two tabs change content below.

Chandra Shekhar

GCP Architect
Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video... Read More about Chandra Shekhar

Latest posts by Chandra Shekhar (see all)