Example of a stored procedure in PHP and MySQL

The following is an example of the stored procedure in PHP and MySQL. I saw the demo of the stored procedure in PHP and MySQL on the relevant website two days ago, let's share it with you. I hope this will help you in your future studies.

PHP and MySQL Stored Procedure Example 1: stored procedure without Parameters

 
 

  
  
$ Conn = MySQL_connect ('localhost', 'root', 'root') or die ("data connection Error !!! ");
  
  
MySQL_select_db ('test', $ conn );
  
  
$ SQL ="
  
  
Create procedure myproce ()
  
  
Begin
  
  
Insert into user (id, username, sex) VALUES (NULL,'s, '0 ');
  
  
End;
  
  
";
  
  
MySQL_query ($ SQL );
 
 

Create a myproce Stored Procedure

 
 

  
  
$sql = "call test.myproce();";  
  
  
MySQL_query($sql); 
 
 

When the stored procedure of myproce is called, a new record is added to the database.

PHP and MySQL Stored Procedure Example 2: Stored Procedure of input parameters

 
 

  
  
$sql = "  
  
  
create procedure myproce2(in score int)  
  
  
begin  
  
  
if score >= 60 then  
  
  
select 'pass';  
  
  
else  
  
  
select 'no';  
  
  
end if;  
  
  
end;   
  
  
";  
  
  
MySQL_query($sql); 
 
 

Create a stored procedure for myproce2

 
 

  
  
$sql = "call test.myproce2(70);";  
  
  
MySQL_query($sql); 
 
 

Calling the stored procedure of myproce2 has no effect. You can see the result in cmd.

PHP and MySQL Stored Procedure Example 3: Stored Procedure of outgoing Parameters

 
 

  
  
$sql = "  
  
  
create procedure myproce3(out score int)  
  
  
begin  
  
  
set score=100;  
  
  
end;   
  
  
";  
  
  
MySQL_query($sql); 
 
 

Create a stored procedure for myproc4

 
 

  
  
$sql = "call test.myproce3(@score);";  
  
  
MySQL_query($sql); 
 
 

Call the stored procedure of myproc4

 
 

  
  
$result = MySQL_query('select @score;');  
  
  
$array = MySQL_fetch_array($result);  
  
  
echo '<pre>';print_r($array); 
 
 

PHP and MySQL Stored Procedure Example 4: inout Stored Procedure of outgoing Parameters

 
 

  
  
$sql = "  
  
  
create procedure myproce4(inout sexflag int)  
  
  
begin  
  
  
SELECT * FROM user WHERE sex = sexflag;  
  
  
end;   
  
  
";  
  
  
MySQL_query($sql); 
 
 

Create a myproce4 Stored Procedure

 
 

  
  
$sql = "set @sexflag = 1";  
  
  
MySQL_query($sql); 
 
 

Set gender parameter to 1

 
 

  
  
$sql = "call test.myproce4(@sexflag);";  
  
  
MySQL_query($sql); 
 
 

Call the stored procedure of myproce4. See the result in cmd.