In this post i will explain how to create store procedure in mysql and how to call store procedure in PHP for select, insert, update and delete data from mysql table. A stored procedure has a header and a body using to declarative SQL statements stored inside the database catalog.
There are three different ways to pass parameters in store procedures in mysql:
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
First create user table with id, name and email fields in your test database with mysql. Open phpmyadmin and go to the SQL tabs. Now copy paste below code and execute this query. After successfully execution of below query users table will be created in your selected database.
1 2 3 4 5 6 | CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(50) UNIQUE ); |
Now again go to the SQL tab in phpmyadmin and copy paste below code, then execute query. Now you have created a stored procedure named “UserInfo” which will select all rows from users tables.
1 2 3 4 5 6 | DELIMITER // CREATE PROCEDURE or replace UserInfo() BEGIN SELECT name,email FROM users END |
Create config.php file for configure database connection with your mysql database. Add below code in your config.php file and save.
1 2 3 4 5 6 7 8 9 10 11 12 13 | < ?PHP //connect to database $conn=mysql_connect("hostname","database username","database password") or die(mysql_error()); //select your database name $dbc=mysql_select_db("testdb",$conn); if (!$dbc) { die('Could not select: ' . mysql_error()); } ?> |
Create index.php file and copy paste below code and save it. In below code first include config.php to connect your PHP code with mysql database, then call your procedure by using call UserInfo().
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | < ?PHP //include db config file include('config.php'); //call the procedure $sql=mysql_query('call UserInfo()'); if (!$sql) { die('syntax error in procedure: ' . mysql_error()); } while($row=mysql_fetch_object($sql)): echo $row->name.'<br>'; echo $row->email.'<br>'; endwhile; ?> |
how to insert data using stored procedure
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DELIMITER // CREATE PROCEDURE insert(IN name VARCHAR(50),IN email VARCHAR(50)) BEGIN SET @username=username; SET @name=name; PREPARE STMT FROM "INSERT INTO users(username,name) VALUES (?,?)"; EXECUTE STMT USING @username,@name; END |
Insert.php page
After above steps now create insert.php file and copy paste below code. Now go to your web browser and run the insert.php page. After insert.php page runed value inserted in users table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <?php //include db config file include('config.php'); $name='freewebmentor'; $sql=mysql_query('CALL insert("$name","$email")'); if (!$sql) { die('Oops something wrong : ' . mysql_error()); } ?> |
Thanks for giving you valuable time on Freewebmentor community. Do like & share with you friends using social media if you liked.
If you like FreeWebMentor and you would like to contribute, you can write an article and mail your article to [email protected] Your article will appear on the FreeWebMentor main page and help other developers.
Article Tags: create and call procedure in mysql, create parameters in stored procedure, how to create store procedure, mysql call procedure in select, mysql create procedure example, mysql create procedure parameters, mysql delimiter, mysql how to pass parameters, mysql stored function example, php call mysql procedure, show procedures in mysql, store procedure in mysql
