create and use mysql stored procedure with php.

create and use mysql stored procedure with php

create and use mysql stored procedure with php

In this tutorial let us check how to create and use mysql stored procedure with php. When creating php web application that need to have database support we usually create sql queries in in the php code itself and use these code for getting our work done. But mysql provide an easy way to put all sql code in mysql itself, instead of putting the sql codes in php. This feature is called stored procedure. By using stored procedure our web application will more faster than putting the codes in php.

What is stored procedure.

Stored procedure are precompiled sql queries which will help to perform sql operation easly. Because of the sql statements are precompiled while calling sql stored procedure we can avoid the compilation time of that sql query. hence we will get result easily.

How to create a store procedure in mysql.

Now let us check how to create a simple store procedure in mysql. Before that let us create a database table. using store procedure let us check how to get result form this table.


  CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Let us insert some demo values to the table.

INSERT INTO `users` (`id`, `email`, `password`, `first_name`, `last_name`) VALUES
(1, ‘[email protected]’, ’123456′, ‘tekbrand’, ‘tb’),
(2, ‘[email protected]’, ’654321′, ‘testFname’, ‘testLname’),
(3, ‘[email protected]’, ’123456789′, ‘test1′, ‘t2′),
(4, ‘[email protected]’, ’1234567′, ‘testasdf’, ‘tasd’),
(5, ‘[email protected]’, ‘pass123′, ‘demofname’, ‘demolname’);

So let us create a store procedure that will call all users in the users table.

DELIMITER $$
DROP PROCEDURE IF EXISTS `get_all_user`$$
CREATE PROCEDURE  `get_all_user`()
BEGIN
SELECT id,email,first_name,last_name
FROM users;
END $$
DELIMITER;

The above sql statement will create a store procedure called get_all_user. This will return the users information like id,email,first_name,last_name in the database.

Calling the mysql store procedure in php.

Let us check how to call the store procedure in php. I’m now using mysqli for connecting to database.


$connection = mysqli_connect("localhost", "root", "";, "test");
$result=mysqli_query($connection, "CALL get_all_user") or die("failed");
    while($res=mysqli_fetch_array($result)){

    print_r($res);

    }

By calling the get_all_user you will get information of all users in the users table.

In the above example we learned how to get how to get the list of users using store procedure.

Now let us create a store procedure to get a single users information. Let us create a store procedure that can help you to get user information by using users email address and password. this can be helpful for login check.

DELIMITER $$
DROP PROCEDURE IF EXISTS `login_check`$$
CREATE PROCEDURE  `login_check`
(
IN uemail VARCHAR(255),
IN upassword VARCHAR(100)
)
BEGIN
SELECT id,email,first_name,last_name
FROM users
WHERE email = uemail
AND password = upassword;
END $$
DELIMITER ;

The above store procedure statement will return user information based on email address and password.

Let us check how to use this in php.

 
$connection = mysqli_connect("localhost", "root", "","test");
$result=mysqli_query($connection, "CALL login_check('[email protected]' , '123456' )") or die("failed");
$res=mysqli_fetch_array($result);
print_r($res);

Now let us create mysql store procedure for insert statement.
This store procedure statement will insert a user data into the database.

DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_user`$$
CREATE PROCEDURE  `insert_user`
(
IN uemail VARCHAR(255),
IN upassword VARCHAR(255),
IN ufirst_name VARCHAR(255),
IN ulast_name VARCHAR(255)
)
BEGIN
INSERT INTO `users` (`email`,`password`,`first_name`,`last_name`)  VALUES (uemail,upassword,ufirst_name,ulast_name);
END $$
DELIMITER;

In the above store procedure we need to pass email,password,first_name,last_name so it will insert a new user into the database.

let us check how to call this procedure.
$connection = mysqli_connect(“localhost”, “root”, “”;, “test”);
$result=mysqli_query($connection, “CALL insert_user(‘[email protected]’,’123456′,’test_fmame’,’test_lname’)”) or die(“failed”);

 

Now let us create a store procedure for updating the user information.

DELIMITER $$
DROP PROCEDURE IF EXISTS `update_user_data`$$
CREATE PROCEDURE  `update_user_data`
(
IN uid INT,
IN ufirst_name VARCHAR(255),
IN ulast_name VARCHAR(255)
)
BEGIN
UPDATE users SET first_name = ufirst_name,last_name = ulast_name WHERE id = uid;
END $$
DELIMITER;

The above store procedure will update the user information in the users table.
using the mysql store procedure in php.

$connection = mysqli_connect("localhost", "root", "";, "test");
$result=mysqli_query($connection, "CALL update_user_data('4','testasdf123','tasd123')") or die("failed");

Now let us create a mysql store procedure for deleting a user record from database.

DELIMITER $$
DROP PROCEDURE IF EXISTS `delet_user`$$
CREATE PROCEDURE  `delet_user`
(
IN uid INT
)
BEGIN
DELETE FROM `users` WHERE `id` = uid;
END $$
DELIMITER;

Using the delet store procedure in php.

$connection = mysqli_connect("localhost", "root", "";, "test");
$result=mysqli_query($connection, "CALL delet_user('6')") or die("failed");

So Here I explained how to create a store procedure for select/insert/update/delete statement. Hop this will be helpful for you.