What is Stored Procedure in MYSQL

MySQL: Stored Procedures in MySQL and PHP

PHP Programming

This tutorial will provide you with an understanding of MySQL stored procedures and guide you through the process of calling them using PHP PDO. We will cover various methods for invoking stored procedures, including handling result sets and managing input/output parameters.

Without any further delay, let’s begin the discussion.

Before proceeding further, I want to let you know that if you are not familiar with PHP PDO or CRUD operations in PHP with PDO, please read the article by clicking on CRUD Operations in PHP with PDO.

What is Stored Procedures

In MySQL, a stored procedure is a predefined, reusable collection of SQL statements and procedural code designed to execute specific tasks within the MySQL database. This robust database feature serves to streamline intricate database operations, boost performance, and fortify security.

In simple terms, a MySQL stored procedure is like a ready-made recipe for the database. It’s a set of instructions that you can use over and over again to do specific things in the database, making it easier to handle complex tasks, speed up operations, and make things more secure.

Stored procedures can accept input parameters, execute SQL queries, and return results. They are typically used for tasks such as data manipulation, data validation, or complex business logic.

Here are some key characteristics and benefits of stored procedures in MySQL:

  • Modularity: Stored procedures allow you to encapsulate a series of SQL statements into a single unit, making your code more modular and easier to maintain.
  • Security: They enhance security by limiting direct access to database tables and allowing controlled access only through the stored procedure. This helps prevent SQL injection attacks.
  • Performance: Stored procedures are precompiled and stored in the database, which can improve query performance as they can be executed more efficiently.
  • Reusability: You can reuse stored procedures across multiple parts of your application, reducing code duplication and promoting consistency.
  • Abstraction: Stored procedures abstract the underlying database structure, making it easier to make changes to the database schema without affecting application code.

How to Create Stored Procedure

In MySQL involves using the CREATE PROCEDURE statement. Here’s a basic example of how to create a simple stored procedure: 

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    SELECT * FROM your_table_name;
END //
DELIMITER ;

Let me break down the components of this example:

  • DELIMITER //: This sets a custom delimiter (//) so that MySQL doesn’t interpret ; as the end of the procedure. This allows you to include ; within the procedure.
  • CREATE PROCEDURE my_stored_procedure(): This is where you define the name of your stored procedure, my_stored_procedure, and you can also specify any input parameters within the parentheses if needed.
  • BEGIN and END: These keywords enclose the body of your stored procedure. All the SQL statements for your procedure should be placed between these two keywords.
  • Inside the procedure body, you can include any SQL statements you need. In this example, it’s a simple SELECT statement, but you can have more complex logic with multiple SQL statements.
  • END //: Marks the end of the procedure definition.
  • DELIMITER ;: This resets the delimiter back to the default ; for future SQL statements outside of the procedure.

To create the stored procedure, you can execute these SQL statements in a MySQL client, such as phpMyAdmin or through the MySQL command-line interface.

In this example, we are using the ‘users’ table from our ‘appwebrestapi’ database.

Here, we are creating a straightforward stored procedure called ‘get_all_users()’ to retrieve all user data from the database.

DELIMITER //
CREATE PROCEDURE get_all_users()
BEGIN
    SELECT * FROM users;
END;
DELIMITER ;

How to Call a Stored Procedure 

CALL `get_all_users`();


Implementation of a stored procedure using PHP.

First of all, we need to connect to the database server using the PDO driver.

Configurations.php

<?php 
   // DEVELOPEMENT SERVER DETAILS ... 
   $DATABASE_SERVER_IP = "localhost";
   $DATABASE_USER_NAME = "root";
   $DATABASE_USER_PASSWORD="";
   $DATABASE_NAME="appwebrestapi";
?>

DBConnect.php

<?php 
require_once  'Configurations.php';
try {
    $con = new PDO(
        "mysql:host=$DATABASE_SERVER_IP;
         dbname=$DATABASE_NAME", 
         $DATABASE_USER_NAME, 
         $DATABASE_USER_PASSWORD
        );
    // set the PDO error mode to exception
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //echo "Connected successfully";
  } catch(PDOException $ex) {
    echo "Connection failed: " . $ex->getMessage();
  }
Download Source Code

Method 1 – A straightforward approach to call a stored procedure without any IN, OUT parameters, or results.

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    try {
        // Calling a procedure to select the data 
        include 'DBConnect.php';
        $CALL_PROCEDURE  =  "CALL `get_all_users`();";
        $select__record__statement = $con->prepare($CALL_PROCEDURE);
        $select__record__statement->execute();
        $user__data = $select__record__statement->fetchAll(PDO::FETCH_ASSOC);
        if ($user__data) {
            http_response_code(200);
            $server__response__success = array(
                "code" => http_response_code(),
                "status" => true,
                "message" => sizeof($user__data) . " Records Found",
                "data" => $user__data
            );
            echo json_encode($server__response__success);
        } else {
            http_response_code(404);
            $server__response__error = array(
                "code" => http_response_code(),
                "status" => false,
                "message" => "No Records Found"
            );
            echo json_encode($server__response__error);
        }
        $con = null; // close the database connection
    } catch (Exception $ex) {
        http_response_code(404);
        $server__response__error = array(
            "code" => http_response_code(),
            "status" => false,
            "message" => "Opps!!! Something went wrong " . $ex->getMessage()
        );
        echo json_encode($server__response__error);
    }
} else {
    http_response_code(404);
    $server__response__error = array(
        "code" => http_response_code(),
        "status" => false,
        "message" => "Invalid Request"
    );
    echo json_encode($server__response__error);
}

Output of the Program

How to Call a stored procedure from MYSQL | MySQL: Stored Procedures in MySQL and PHP

Method 2 – Creating/Calling a stored procedure with IN parameters.

In this procedure, we will filter data using an IN parameter. Here, we will pass the user’s email address as a parameter and use it to filter the data.

DELIMITER $$
CREATE PROCEDURE `get_users_with_filter`(IN `input_email` TEXT)
BEGIN
SELECT * FROM users WHERE users.email_id=input_email;
END ;
DELIMITER ;
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    if (!empty($_GET['emailID'])) {
        try {
            $email_ID = $_GET['emailID'];
            // Calling a procedure to select the data 
            include 'DBConnect.php';
            $CALL_PROCEDURE_WITH_ONE_PARAMETER  =  "CALL `get_users_with_filter`(:email_ID);";
            $select__record__statement = $con->prepare($CALL_PROCEDURE_WITH_ONE_PARAMETER);
            $select__record__statement->bindParam(':email_ID', $email_ID, PDO::PARAM_STR);
            $select__record__statement->execute();
            $user__data = $select__record__statement->fetchAll(PDO::FETCH_ASSOC);
            if ($user__data) {
                http_response_code(200);
                $server__response__success = array(
                    "code" => http_response_code(),
                    "status" => true,
                    "message" => sizeof($user__data) . " Records Found",
                    "data" => $user__data
                );
                echo json_encode($server__response__success);
            } else {
                http_response_code(404);
                $server__response__error = array(
                    "code" => http_response_code(),
                    "status" => false,
                    "message" => "No Records Found"
                );
                echo json_encode($server__response__error);
            }
            $con = null; // close the database connection
        } catch (Exception $ex) {
            http_response_code(404);
            $server__response__error = array(
                "code" => http_response_code(),
                "status" => false,
                "message" => "Opps!!! Something went wrong " . $ex->getMessage()
            );
            echo json_encode($server__response__error);
        }
    } else {
        http_response_code(404);
        $server__response__error = array(
            "code" => http_response_code(),
            "status" => false,
            "message" => "Invalid Parameter"
        );
        echo json_encode($server__response__error);
    }
} else {
    http_response_code(404);
    $server__response__error = array(
        "code" => http_response_code(),
        "status" => false,
        "message" => "Invalid Request"
    );
    echo json_encode($server__response__error);
}

Method 3 – Creating/Calling Procedure with OUT Parameter

DELIMITER $$
CREATE PROCEDURE `get_users_count`(
	OUT totalUsers INT
)
BEGIN
	SELECT COUNT(users.email_id)
	INTO totalUsers
	FROM users;
END$$
DELIMITER ;

Implementation of OUT Params using PHP 

 try {
        // Calling a procedure to select the data 
        include 'DBConnect.php';
        $CALL_PROCEDURE_WITH_OUT_PARAM  =  "CALL `get_users_count`(@userCount);";
        $select__record__statement = $con->prepare($CALL_PROCEDURE_WITH_OUT_PARAM);
        $select__record__statement->execute();

        $SELECT_DATA  =  "SELECT @userCount AS `totalUsers`;";
        $Select__data__record__statement = $con->prepare($SELECT_DATA);
        $Select__data__record__statement->execute();
        $user__data = $Select__data__record__statement->fetchAll(PDO::FETCH_ASSOC);
        if ($user__data) {
            http_response_code(200);
            $server__response__success = array(
                "code" => http_response_code(),
                "status" => true,
                "message" => sizeof($user__data) . " Records Found",
                "No_of_users" => $user__data
            );
            echo json_encode($server__response__success);
        } else {
            http_response_code(404);
            $server__response__error = array(
                "code" => http_response_code(),
                "status" => false,
                "message" => "No Records Found"
            );
            echo json_encode($server__response__error);
        }
        $con = null; // close the database connection
    } catch (Exception $ex) {
        http_response_code(404);
        $server__response__error = array(
            "code" => http_response_code(),
            "status" => false,
            "message" => "Opps!!! Something went wrong " . $ex->getMessage()
        );
        echo json_encode($server__response__error);
    }

Method 4: Creating/Calling a procedure with an IN Parameter and Returning a Result

DELIMITER $$
CREATE PROCEDURE get_all_user_having_same_email (
	IN  emailID VARCHAR(25),
	OUT totalUsers INT
)
BEGIN
	SELECT COUNT(users.id)
	INTO totalUsers
	FROM users
	WHERE users.email_id = emailID;
END$$
DELIMITER ;
CALL `get_all_user_having_same_email`('pradeep@appwebcoders.in', @p1); SELECT @p1 AS `totalUsers`;

Implementation with PHP 

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    if (!empty($_GET['emailID'])) {
        try {
            $email_ID = $_GET['emailID'];
            // Calling a procedure to select the data 
            include 'DBConnect.php';
            $CALL_PROCEDURE_QUERY_ONE  =  "CALL `get_all_user_having_same_email`(:email_ID, @userCount); ";
            $select__record__statement = $con->prepare($CALL_PROCEDURE_QUERY_ONE);
            $select__record__statement->bindParam(':email_ID',$email_ID,PDO::PARAM_STR);
            $select__record__statement->execute();

            $SELECT_DATA  =  "SELECT @userCount AS `totalUsers`;";
            $Select__data__record__statement = $con->prepare($SELECT_DATA);
            $Select__data__record__statement->execute();
            $user__data = $Select__data__record__statement->fetchAll(PDO::FETCH_ASSOC);
            if ($user__data) {
                http_response_code(200);
                $server__response__success = array(
                    "code" => http_response_code(),
                    "status" => true,
                    "message" => sizeof($user__data) . " Records Found",
                    "data" => $user__data
                );
                echo json_encode($server__response__success);
            } else {
                http_response_code(404);
                $server__response__error = array(
                    "code" => http_response_code(),
                    "status" => false,
                    "message" => "No Records Found"
                );
                echo json_encode($server__response__error);
            }
            $con = null; // close the database connection
        } catch (Exception $ex) {
            http_response_code(404);
            $server__response__error = array(
                "code" => http_response_code(),
                "status" => false,
                "message" => "Opps!!! Something went wrong " . $ex->getMessage()
            );
            echo json_encode($server__response__error);
        }
    } else {
        http_response_code(404);
        $server__response__error = array(
            "code" => http_response_code(),
            "status" => false,
            "message" => "Invalid Parameter"
        );
        echo json_encode($server__response__error);
    }
} else {
    http_response_code(404);
    $server__response__error = array(
        "code" => http_response_code(),
        "status" => false,
        "message" => "Invalid Request"
    );
    echo json_encode($server__response__error);
}

Conclusion

In this blog post, we’ve delved into the world of stored procedures in MySQL and how they can be effectively utilized in PHP applications. We’ve covered the fundamental concepts of stored procedures, including their creation, execution, and the use of IN and OUT parameters.

By harnessing the power of stored procedures, you can achieve several advantages for your MySQL-powered PHP projects. These include improved database security, code organization, and enhanced performance through reduced network traffic.

Furthermore, we explored the step-by-step process of creating and calling stored procedures from PHP code, demonstrating how to leverage these database assets to streamline operations and enhance data management within your web applications.

As you continue to explore the capabilities of MySQL stored procedures, you’ll find they provide a valuable toolset for optimizing your PHP-based web applications, making them more efficient, secure, and maintainable. Incorporating stored procedures into your development workflow can lead to more robust and scalable systems, ultimately enhancing the user experience and the overall success of your web projects.

Thank you for taking the time to read our blog post on MySQL stored procedures in MySQL and PHP. Your feedback is important to us, as it helps us improve our content and better serve your needs.

We would greatly appreciate your thoughts and suggestions. If you have any feedback, questions, or topics you’d like us to cover in future blog posts, please don’t hesitate to share them with us. Your input allows us to create content that is more valuable and relevant to you.

Once again, thank you for being a part of our community, and we look forward to hearing from you!

Node JS, Android, PHP, JAVA, AJAX, ReactJS, IoT

Performance-driven & Result Oriented Software Professional with extensive experience in designing, analysing, developing, delivering and supporting fast paced Web and Mobile Applications..

Add a Comment

Your email address will not be published. Required fields are marked *