Tutorial : How We Can Use Mysql Stored Procedure in PHP

Are you writing Stored Procedures if not please take a look at this post. Stored procedures can help to improve web application performance and reduce database access traffic. In this post I want to explain how to create and call the stored procedures from database server.

Database

users table contains username and name.

CREATE TABLE users

(

id INT PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(50) UNIQUE,

name VARCHAR(50),

);

Results.php (Direct database server access)

Calling SQL statements directly. Here displaying the users content with PHP.

<?php

include('db.php'); // Check code below of the post.

$sql=mysql_query("SELECT user,name FROM users");

while($row=mysql_fetch_array($sql))

{

echo $row['user'].'--'.$row['name'].'</br>';

}

?>

How to Create Stored Procedure

You can create stored procedures that run on your database server. Stored Procedure nameusers(). Just like SQL statements. 

DELIMITER // 

CREATE PROCEDURE users() 

SELECT username,name FROM users;

How to Call Stored Procedure

Results.php (With stored procedures)

Notice that here mysqli(MySQL Improved Extension)

<?php

include("newdb.php");

$sql=mysqli_query($connect,"CALL users()");

while($row=mysqli_fetch_array(sql))

{

echo $row['user'].'--'.$row['name'].'';

}

?>

newdb.php (Stored Procedure)

You have to include this file every stored procedure call. Why because call automatically closing the database connection. 

<?php

$connect= mysqli_connect('localhost','username','password','database'); 

if (!$connect)

{

printf("Can't connect to MySQL Server.", mysqli_connect_error());

exit;

}

?>

Stored Procedure Input

Normal Way

insert procedure IN - Input , name and datatype.

DELIMITER // 

CREATE PROCEDURE insert(IN username VARCHAR(50),IN name VARCHAR(50))

INSERT INTO users(username,name) VALUES (username,name);

Better Way

I recommend you to create stored procedures following statements.

DELIMITER // 

CREATE PROCEDURE insert(IN username VARCHAR(50),IN name VARCHAR(50))

BEGIN

SET @username=username; 

SET @name=name;

PREPARE STMT FROM 

"INSERT INTO users(username,name) VALUES (?,?)";

EXECUTE STMT USING @username,@name; 

END

insert.php

Here inserting values into users table with calling insert() procedure. 

<?php

include("newdb.php");

$username='9lessons';

$name='Srinivas Tamada';

$sql=mysqli_query($connect,

"CALL insert('$username','$name')");

?>

db.php

Database configuration code.

<?php

$mysql_hostname = "localhost";

$mysql_user = "username";

$mysql_password = "password";

$mysql_database = "database";

$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) 

or die("Opps some thing went wrong");

mysql_select_db($mysql_database, $bd) or die("Opps some thing went wrong");

?>

Put simply, a Stored Procedure ("SP") is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages.

In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP.

Note: We are not going to cover the full aspect of the SP here. The official MySQL document should always be the place for reference.

SP are also available in other common database servers (Postgre for example) so what we will discuss here can be applied to those as well.

Why Stored Procedures are recommended

Most of us are quite familiar with the normal setup to build a database application: creating database, creating tables, set up indexes, CRUD the data, issue queries from the client side and do further processing if necessary.

That workflow works fine in most cases but there is one important aspect of database programming missing: the Stored Procedure.

There are at least four advantages I can think of to use an SP in a database application.

Firstly, it reduces the network traffic and overhead. In a typical PHP database web application, there are four layers:

• The client layer, which is normally a web browser. It receives user interactions and presents the data in a UI.
• The web server layer, which handles and dispatches user requests and sends back responses to the client layer.
• The PHP layer, which handles all PHP interpretation, does the application logic and generates the PHP part of response.
• The database layer, which handles all database queries, including but not limited to a SELECT query, anINSERT statement, etc.

In a typical environment, these layers will most likely not reside on one single machine, maybe not even in one network, for larger applications.

Although network speed has tremendously increased in the past few years, it is still the slowest and most unreliable compared to other ways of transferring the data (CPU cache, memory, hard disk, etc). So, to save bandwidth and increase robustness, it is sometimes a good idea to have more processing and logic done on the server side (in particular, the MySQL server) and have less data transferred through the network.

Secondly, it improves the performance. SP is stored and run directly in the MySQL server. It can be pre-compiled and analyzed by the database server. This is quite different from issuing the same query from the client side, where the query will be parsed by database drivers, analyzed and optimized (if possible) every time the query statement is called. This is somehow quite like the interpreted language execution (at the client end) and the compiled language execution (at the database server end). And we know a compiled program will run faster.

Third, Write Once and Execute Anywhere. SQL is standard and purely 100% platform independent. It only relies on the database server. Consider how many different languages/libs there are that we can use to deal with the database. It increases efficiency to put the data retrieving and processing at the server end instead of writing the same processing logic in a different syntax provided by all these languages/libs, if the data processing logic is so commonly used.

Last but not least, SP is a fundamental aspect of database security.

Let's consider a simple database setup. In a human resource information system (HRIS), it is reasonable to assume that there exists a table holding the salary information of each employee. An HR employee should have the right to grab some figures out of this table: total salary, average salary, etc but this employee should not see the detailed salary of each employee as this information will be too sensitive and should only be available to a few.

We know MySQL has a comprehensive privilege control. In this case, it is obvious that we can't even grantSELECT privilege to this HR employee (which, if we do, means he/she can see the detailed salary of everyone). But if he/she can't access the salary table, how can this employee get the aggregation information related to salary? How can we allow the employee to grab that information without compromising the HR policy?

The answer is using a Stored Procedure that returns the required information and grants that employee theEXECUTE privilege. (For a detailed list and explanation of MySQL privileges, please consult the official documentation. The link here is for MySQL 5.6. Please replace 5.6 with the version you are using.)

SP is now a bridge, bridging the user (our HR employee) and the table (salary), to which the user has no direct access.

That's it! With SP, we can get the user to accomplish the task without compromising the database security (and HR policy)!

Drawbacks of using Stored Procedures

After naming all the advantages of using an SP, we need to be clear about some of the drawbacks and see if there are ways to improve.

• No version control on SP itself. When an SP is modified, it is modified, no historical trails can be kept in the server side. It may create some frustrations when the user would like to rollback a change. My suggestion is to write the SP in your client side and put it under version control. When the SP is ready, it is easy to copy the code into, say MySQL Workbench and create it at the server side. By doing so, we can have some degree of version control.
• No easy way to "synchronize" the changes applied and force everyone to use the latest version, in particular, when each of the team member has his/her own local database for development and testing purposes. Version control may be the solution but still requires manual intervention by updating the local copy of the SP in the local db server. Another way is to use "mocking". The team members can be divided so that at least one person will focus on the maintenance of the SP and the implementation of the calling to SP in the code. All others that need the results from the SP can develop and test their portion using mocking objects, i.e, always assuming the "faked" call to the SP will return a desired result. In a later stage, merging can be done to discard the mocking code.
• Hard to backup/export. The SP is on the server side. Regular developers will only have basic privileges (SELECT, EXECUTE, etc) and no admin rights to backup and export. In a way, I won't call it a drawback but rather a fundamental aspect of db security. There is no way, and it is not recommended to get around this. It is suggested that, in a team, a dedicated DB admin will be appointed to do such jobs. A regular db backup can also serve the backup/export (and import) purpose.