Back to Top

How To use Store Procedure with PDO

To use Store Procedure with PDO

Many databases support the concept of prepared statements.Prepared statements are useful to execute SQL statements with some patterns and optimize the query.Today I am going to add the post for PDO.It is my first post for PDO.I think almost all developers have an idea about store procedure. I am going to cover store procedure with PDO that is how we can use store procedure with PDO.

Many of the databases support store procedures.Store procedure is a one kind of script that are executed on your database typically in a database(MySQL).Stored Procedure allows us to manipulate the data close to the location where the data is held and reduce the bandwidth.A stored procedure is stored in a database that can be called by the database engine and also reduces network traffic overhead.

What is Store Procedure?

Store Procedure maintains the separation of the data from the script logic and allows multiple systems to access the data in a uniform manner so its save you valuable coding and debugging time.Finally, stored procedures increase query speeds with the use of predefined execution code and with Stored Procedure we can prevent direct interaction with the data.

NOTE: Each database have different store procedure language so check your database syntax for store procedure first.

Example to use Store Procedure with PDO

Let’s start with PDO.To use a PDO with store procedure is very simple.Let me explain with example so you will clear with

  SELECT *  FROM category;
  END //

Above is the simple store procedure to retrieve data from table category.Here is code with which you can run store procedure directly in MySQL:

CALL GetAllCategory();

Once GetAllCategory Store Procedure will be created that can be called at any time. It returns all categories from the table with executing above code.The Store Procedure allows you to use a data in the more powerful way in order to meet business needs.

// PDO Connection to the database
	$dbh = new PDO('mysql:host=;dbname=bhumi_citest', 'root', 'mysqldba');
}catch (PDOException $e) {
	print "Error!: " . $e->getMessage();

// Prepare stored procedure call
$sth = $dbh->prepare('CALL GetAllCategory()');

// Call the stored procedure
print "Executing stored procedure with PDO...

"; $res = $sth->execute(); if ($res){ // Print inout and output parameter data echo ""; while( $row = $sth->fetchObject() ){ echo ""; } echo "
Category IDCategory Name
"; // Close connection $dbh = null; }else{ $arr = $sth->errorInfo(); echo "Execution failed with MYSQL: " . $arr[0] . "\n"; die(); }

Must READ:
How to use Triggers in MySQL
Database BackUp Script
NoSQL and MongoDB

In the example above, which was made for MySQL, you’ll notice that I have used the SELECT statements in store procedure but you can use DECLARE, SET as per your query. Generally, these are needed in any MySQL stored procedure to retrieve data. Nothing special is needed to retrieve the data from the stored procedure, we just call the Store procedure and pass store procedure with method PREPARE to grab the value returned from the SELECT statement in the stored procedure.

If you have any question or query, please leave your comments.I would be happy help you in any issue.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to Create a Zip File In PHP

Posted on 7 years ago


Hello world

Updated 1 month ago

Nick Carter

In depth Understanding of Laravel Core

Updated 3 months ago


ECMAScript 6 New Features – 2

Updated 1 year ago


The Reader’s Poll – June 2015

Posted on 4 years ago


The Reader’s Poll – August 2014

Posted on 5 years ago


The Reader’s Poll – June 2014

Posted on 5 years ago


The Readers’ Poll – May 2014

Posted on 5 years ago


The Readers’ Poll – August 2013

Posted on 6 years ago