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
1 2 3 4 5 6 7 8 | DELIMITER // CREATE PROCEDURE GetAllCategory() BEGIN SELECT * FROM category; END // DELIMITER ; |
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:
1 2 3 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | // PDO Connection to the database try{ $dbh = new PDO('mysql:host=192.168.101.11;dbname=bhumi_citest', 'root', 'mysqldba'); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); }catch (PDOException $e) { die(); } // Prepare stored procedure call $sth = $dbh->prepare('CALL GetAllCategory()'); // Call the stored procedure print "Executing stored procedure with PDO...<br/><br/>"; $res = $sth->execute(); if ($res){ // Print inout and output parameter data echo "<table border='1'><tr><th>Category ID</th><th>Category Name</th></tr>"; while( $row = $sth->fetchObject() ){ echo "<tr><td>".$row->id."</td><td>".$row->cat_name."</td></tr>"; } echo "</table>"; // 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.
