MySQL stored procedures in PHP with examples

Question

Can someone help me to create stored procedures in MySQL and call them from PHP?

Answer 1

You can create procedure very easily in MySQL -- Syntax :-

`//add a delimiter; 
Create Procedure <procedure_name>(parameters "IN(for using inside the procedure)" and "OUT(for returning)")
Begin
   // doing some thing.
end;
//end delimiter;`

Call Procedure - `CALL <procedure_name>(parameter1, parameter2, ....)`

Example -

`DELIMITER //  
CREATE PROCEDURE EmployeeName(IN EId INT)  
BEGIN  
    SELECT EmpName FROM Employee WHERE EmpId = EID;  
END//`

Calling- assume i want to know the name of employee id "10", so --

CALL EmployeeName(10);

Answer 2

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_user_login` $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_user_login`(
  IN loc_username VARCHAR(255),
  IN loc_password VARCHAR(255)
)
BEGIN

  SELECT user_id,
         user_name,
         user_emailid,
         user_profileimage,
         last_update
    FROM tbl_user
   WHERE user_name = loc_username
     AND password = loc_password
     AND status = 1;

END $$

DELIMITER ;

and call by, mysql_connection specification and

$loginCheck="call sp_user_login('".$username."','".$password."');";

it will return the result from the procedure.