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
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 Conferencing and social media domain. The motive of creating cutehits was just to share the knowledge/solutions I get to know during my day to day life so that if possible I can help someone for same problems/solutions. CuteHits.com is a really a very effort for sharing knowledge to rest of the world. For any query/suggestion about same you can contact me on below details:-
Email: shekharmca2005 at gmail.com
Phone: +91-9560201363
Latest posts by Chandra Shekhar (see all)
- Best practices for micro service design - January 23, 2022
- Spring Boot - January 23, 2022
- Java - January 23, 2022
Recent Comments