Using Stored procedure with mySQL and PHP

Home Resources Articles Using Stored procedure with mySQL and PHP

Using external scripts for complex data handling is often a tedious process. Stored Procedures provide a superior solution by automating tasks directly within the database server. Designing flexible Stored Procedures is crucial, as it simplifies error identification and enables them to execute a wide range of tasks efficiently.

What are Stored Procedures?

A Stored Procedure is a set of pre-compiled SQL commands saved as a single, reusable unit within the database server. Once created, an application can execute complex tasks by simply calling the stored procedure’s name, rather than sending multiple lines of SQL code over the network. This approach significantly reduces network traffic and eases the CPU load on the server.

Key Advantages of Stored Procedures

Improved Performance: Stored procedures are compiled once and stored in executable form, so subsequent calls are executed much faster than sending and compiling the same queries repeatedly.
Reduced Network Traffic: Instead of sending large blocks of SQL code, an application only needs to send the short command to execute the procedure. This is especially beneficial in high-latency environments.
Code Reusability and Centralization: You can write the procedure once and call it from multiple applications or different parts of the same application. This centralizes business logic in the database, making it easier to maintain and update.
Enhanced Security: You can permit users to execute a stored procedure without giving them direct access to the underlying database tables. This prevents malicious or accidental direct data manipulation.
Simplified Development: Complex operations can be encapsulated within a single procedure. This makes the application code cleaner, simpler, and easier to manage, as the complex database logic is handled on the server side.

MySQL Stored Procedures

For a long time, a key feature distinguishing platforms like Oracle and Microsoft SQL Server from MySQL was their robust support for Stored Procedures. That gap has long since closed. Beginning with MySQL 5, Stored Procedures became a standard, fully supported feature, bringing MySQL’s capabilities in line with other enterprise-level database systems.

The syntax for using Stored Procedures is as follows:

-- Stored Procedure Template
CREATE PROCEDURE IF NOT EXISTS sp_name (
    [IN|OUT|INOUT] param_name data_type,
    ...
)
[LANGUAGE SQL]
[DETERMINISTIC | NOT DETERMINISTIC]
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
[SQL SECURITY DEFINER | SQL SECURITY INVOKER]
[COMMENT 'Descriptive comment about the procedure']
BEGIN
    -- Valid SQL statements here
END;

-- Stored Function Template
CREATE FUNCTION IF NOT EXISTS sp_name (
    param_name data_type,
    ...
)
RETURNS return_data_type
[LANGUAGE SQL]
[DETERMINISTIC | NOT DETERMINISTIC]
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
[SQL SECURITY DEFINER | SQL SECURITY INVOKER]
[COMMENT 'Descriptive comment about the function']
BEGIN
    -- Valid SQL statements here
    RETURN value;
END;

Applications of MySQL Stored Procedures

MySQL Stored Procedures are highly versatile and can be effectively applied in a wide range of scenarios, from automating simple tasks to managing complex business logic. They are particularly valuable in the following situations:

Multi-Platform Environments: When your database is accessed by various applications built in different programming languages (like Python, Java, or PHP), stored procedures provide a centralized and consistent way to interact with data. All applications can call the same procedure, ensuring business rules are enforced uniformly.
High-Security Systems: In environments where data security is critical, such as banking or healthcare, stored procedures are essential. They allow you to create a secure API for the database. You can permit users to execute a specific procedure without giving them any direct access to the underlying tables, preventing unauthorized queries or data manipulation.
Performance Optimization: Stored procedures significantly boost performance by reducing network traffic. Instead of an application sending large, complex SQL queries across the network, it sends only a short, single command to execute the procedure, minimizing latency and reducing the load on the database server.

While early versions of MySQL’s stored procedures were less mature than those in other systems, they are now a robust, stable, and essential feature for any serious MySQL developer or administrator.

A simple example for MySQL Stored Procedure

To calculate the area of a circle with a given radius R, the following commands can be given

-- Define the function using modern syntax and delimiters
DELIMITER //

CREATE FUNCTION Area(R DOUBLE) 
RETURNS DOUBLE 
DETERMINISTIC
BEGIN
    DECLARE A DOUBLE;
    SET A = R * R * PI();
    RETURN A;
END //

DELIMITER ;

And the PHP code using mysquli:

<?php
// Connect to MySQL using mysqli
$mysqli = new mysqli("hostname", "username", "password", "database");

// Check for connection error
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}

// Execute the query calling the stored function
$result = $mysqli->query("SELECT Area(22) AS area");

if ($result) {
$row = $result->fetch_assoc();
echo "The area of the circle with radius 22cm is " . $row['area'] . " sq.cm";
} else {
echo "Query failed: " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

Resources