Using Stored Procedures in MySQL with PHP PDO

The connection object $dbo is taken from config.php file.
Sample Student table SQL dump
Stored procedures allow us to encapsulate SQL logic inside MySQL for reuse and optimization. We can call these procedures using PHP PDO, and we will learn how to do that using the $dbo connection object.

1.Creating a Stored Procedure in phpMyAdmin

1.Open phpMyAdmin and select our database.
2.Navigate to the SQL tab and run the following query:

DELIMITER //
CREATE PROCEDURE GetStudentById(IN student_id INT)
BEGIN
    SELECT * FROM student WHERE id = student_id;
END //
DELIMITER ;

3.Click Go to create the procedure.
4.The stored procedure will now be available under the Routines tab. ( Click the database name link if you are at table level. )

Calling a Stored Procedure with PHP PDO

$dbo = new PDO("mysql:host=localhost;dbname=testdb", "userid", "pw");

$stmt = $dbo->prepare("CALL GetStudentById(:id)");
$stmt->bindParam(':id', $student_id, PDO::PARAM_INT);
$student_id = 1;
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Prepared Statement: Use CALL to execute the procedure and pass input parameters securely.
Fetch Results: The fetchAll() method retrieves the result set returned by the procedure.

Types of Stored Procedures

1.Without Parameters:
This type of stored procedure executes a predefined query without requiring any input.

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM student;
END //
DELIMITER ;

2.With Input Parameters:
Takes input parameters to perform a specific action.

DELIMITER //
CREATE PROCEDURE GetStudentById(IN student_id INT)
BEGIN
    SELECT * FROM student WHERE id = student_id;
END //
DELIMITER ;

3.With Output Parameters:
Can return a value to the calling program.

DELIMITER //
CREATE PROCEDURE GetStudentName(IN student_id INT, OUT student_name VARCHAR(100))
BEGIN
    SELECT name INTO student_name FROM student WHERE id = student_id;
END //
DELIMITER ;

Calling Stored Procedure with Output Parameters in PHP PDO

$dbo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");

$stmt = $dbo->prepare("CALL GetStudentName(:id, @name)");
$stmt->bindParam(':id', $student_id, PDO::PARAM_INT);
$student_id = 1;
$stmt->execute();

// Fetch the output parameter
$name_stmt = $dbo->query("SELECT @name AS student_name");
$name_result = $name_stmt->fetch(PDO::FETCH_ASSOC);
echo "Student Name: " . $name_result['student_name'];

Handling Errors with Transactions

Here’s an example of using SIGNAL with the student table to raise a custom error when a specific condition is met, such as when a student's mark is invalid (e.g., a negative mark):

DELIMITER //
CREATE PROCEDURE AddStudent(IN student_name VARCHAR(100), IN student_mark INT)
BEGIN
    -- Check if mark is valid
    IF student_mark < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid mark. Mark cannot be negative.';
    ELSE
        -- Proceed with inserting the student
        INSERT INTO student (name, mark) VALUES (student_name, student_mark);
    END IF;
END //
DELIMITER ;

The PHP PDO part is here .

try {
    // Prepare the stored procedure call
    $stmt = $dbo->prepare("CALL AddStudent(:student_name, :student_mark)");

    // Bind parameters
    $stmt->bindParam(':student_name', $student_name, PDO::PARAM_STR);
    $stmt->bindParam(':student_mark', $student_mark, PDO::PARAM_INT);

    // Set values and execute the procedure
    $student_name = "Alex";
    $student_mark = -85;  // Change to a negative number to trigger the SIGNAL

    $stmt->execute();
    echo "Student added successfully.";
    
} catch (PDOException $e) {
    // Catch any SQLSTATE errors, such as invalid marks
    echo "Error: " . $e->getMessage();
}

In MySQL, internal errors (such as constraint violations or type mismatches) are handled automatically, raising standard SQL error codes. To manage such errors more gracefully in stored procedures, we can use SIGNAL with a specific SQLSTATE to raise custom messages when an internal error occurs. We can also use RESIGNAL within an exception handler to rethrow internal errors or provide custom responses.

Here’s an example of handling internal errors using RESIGNAL:

DELIMITER //
CREATE PROCEDURE AddStudent(IN student_name VARCHAR(100), IN student_mark INT)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Handle any internal error and rethrow a custom error
        RESIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Internal error occurred during student insertion.';
    END;
    
    -- Attempt to insert data, triggering internal errors if constraints are violated
    INSERT INTO student (name, mark) VALUES (student_name, student_mark);
END //
DELIMITER ;

Explanation:
RESIGNAL: Captures any internal SQL errors (like type mismatches or unique constraint violations) and raises a custom error.
Error Handling: If any SQL exception occurs within the procedure, it will raise the custom error message defined in the RESIGNAL block.

Using conditional logic (IF, CASE)

This stored procedure calculates a student’s grade based on their marks using IF conditions.

DELIMITER //
CREATE PROCEDURE GetStudentGrade(IN student_id INT, OUT grade VARCHAR(10))
BEGIN
    DECLARE student_mark INT;
    SELECT mark INTO student_mark FROM student WHERE id = student_id;
    
    IF student_mark >= 90 THEN
        SET grade = 'A';
    ELSEIF student_mark >= 75 THEN
        SET grade = 'B';
    ELSEIF student_mark >= 50 THEN
        SET grade = 'C';
    ELSE
        SET grade = 'F';
    END IF;
END //
DELIMITER ;

We can call the stored procedure in PHP PDO using the following code:

$dbo = new PDO("mysql:host=localhost;dbname=testdb", "userid", "pw");

$student_id = 1;
$stmt = $dbo->prepare("CALL GetStudentGrade(:student_id, @grade)");
$stmt->bindParam(':student_id', $student_id, PDO::PARAM_INT);
$stmt->execute();

// Fetch the output parameter
$grade_stmt = $dbo->query("SELECT @grade AS grade");
$grade_result = $grade_stmt->fetch(PDO::FETCH_ASSOC);

echo "Student Grade: " . $grade_result['grade'];

Output

Student Grade: C

CALL Procedure: We call the GetStudentGrade procedure and pass the input parameter (student_id).
Output Parameter: We use @grade as an output variable to store and retrieve the result.

Getting multiple rows of data

We will give minimum and maximum mark as range to get all records between these values.

DELIMITER //
CREATE PROCEDURE GetStudentsByMarkRange(IN min_mark INT, IN max_mark INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE total_students INT;
    
    SELECT COUNT(*) INTO total_students FROM student WHERE mark BETWEEN min_mark AND max_mark;
    
    loop_label: LOOP
        IF counter >= total_students THEN
            LEAVE loop_label;
        END IF;
        SELECT name, mark FROM student WHERE mark BETWEEN min_mark AND max_mark LIMIT counter, 1;
        SET counter = counter + 1;
    END LOOP;
END //
DELIMITER ;

From PHP side we will call the stored procedure like this.

$min_mark = 40;
$max_mark = 60;

$stmt = $dbo->prepare("CALL GetStudentsByMarkRange(:min_mark, :max_mark)");
$stmt->bindParam(':min_mark', $min_mark, PDO::PARAM_INT);
$stmt->bindParam(':max_mark', $max_mark, PDO::PARAM_INT);
$stmt->execute();

// Fetch all result sets using nextRowset()
do {
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
  // Using foreach looping as we are getting 2 dimensional array 
  foreach ($students as $student) {
   echo "Name: " . $student['name'] . " Mark: " . $student['mark'] . "<br>";
  }
  
  /* // Can use like this also 
	if (!empty($students)) {
    echo "Name: " . $students[0]['name'] . " Mark: " . $students[0]['mark'] . "<br>";
    }
  */
} while ($stmt->nextRowset());

To delete a stored procedure from the MySQL database using PHP PDO, you can execute a DROP PROCEDURE SQL command. Here's how:

PHP PDO Code to Delete a Stored Procedure:

$dbo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");

try {
    $stmt = $dbo->prepare("DROP PROCEDURE IF EXISTS procedure_name");
    $stmt->execute();
    echo "Stored procedure deleted successfully.";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Replace procedure_name with the name of your stored procedure.
The query uses DROP PROCEDURE IF EXISTS to delete the procedure if it exists, preventing errors if the procedure is not found.

Benefits of Stored Procedures for Performance:

Reduced Network Latency: Fewer client-server communications.
Efficient Data Processing: Complex logic is processed on the server.
Reusability: The same logic can be reused across different applications.

Conclusion

Stored procedures significantly streamline complex database logic and boost performance when combined with PHP PDO. By moving key operations into the database, they reduce code redundancy and optimize query execution. Utilizing input or output parameters, along with transactions, ensures secure, consistent data handling. This method minimizes the need for multiple client-server communications, improving both efficiency and security, particularly in high-traffic applications.

Paging using Stored Procedure

Paging of records in MySQL
Paging in MySQL allows us to efficiently display large datasets by splitting the result set into smaller chunks, typically using a LIMIT and OFFSET clause in our query.
Paging of records by using Stored Procedure