Mysql Stored Procedure and Get Procedure Data From PHP

Hi guys, Today we are going to see How to create a stored procedure and get call data from a stored procedure by using PHP. Before going to the topic let see What is stored procedure? and Why we need use stored procedure?. A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.

If you know about PHP function scenario, you can quickly understand this procedure logic. Because both the concept looks like same. Only thing is stored procedure concept will use Direct to the database. 

Why We use stored procedure

Once you created procedure it will be stored in your database. You can call it any number of time. Every procedure has some unique name. Based on that name call your procedure function.

• It can reduce the network traffic time - Load a large number of record in your database it took much time to load on network traffic, By using the procedure it will take less time to execute the query.
• Modular Programming -  you can modify stored procedures independently of the program source code.
• Reuseable - Same procedure you can multiple time with a custom parameter. 

Create Stored Procedure

The Following syntax creates the stored procedure. 

DELIMITER //
CREATE PROCEDURE country_name()
BEGIN
  SELECT name FROM Country;
END //
DELIMITER ;

To test stored procedure execute below statement into the MySQL command interpreter.

CALL country_name();

The procedure statement will execute command line interpreter. Most of them have PHPMyAdmin try to execute procedure statement with PHPMyAdmin SQL compiler.

Before the start, this tutorial Create a new table or if you have existing table use procedure code. later Create a new stored procedure named GetUSersinformation(). The GetUSersinformation() retrieves the all users name list.

SQL Statment

DELIMITER //
CREATE PROCEDURE GetUSersinformation()

BEGIN
  SELECT name FROM users_information;

END //
DELIMITER ;

• GetUSersinformation() - Stored Procedure name 
• DELIMITER - Pass the entire stored program definition to the server.
• BEGIN, END - Procedure code start and end.

Phpmyadmin Complier

How to call a stored procedure in PHP

However retrieving SQL statement. Follow the same way to get procedure CALL(); Follow the below PHP script to retrieve the stored procedure SQL information.

Create a new PHP file named  index.php  with the following code:

<?php 
  //connect to database
  $connection = mysqli_connect("localhost", "root", "", "mostlikers");
  //run the store pro
  $result = mysqli_query($connection,"CALL GetUSersinformation()") or 
          die("Query fail: " . mysqli_error());

  //loop the result set
  while ($row = mysqli_fetch_array($result)){   
    echo $row['name'].'<br/>';
  }

?>

In this tutorial, you have learned how to call MySQL stored procedures using PHP. The upcoming tutorial will guide you with a parameter way get procedure data.