Browse DevX
Sign up for e-mail newsletters from DevX


Executing Stored Procedures and Functions From PHP in Windows : Page 3

Discover how to call stored procedures and functions in MySQL from PHP using three database extensions: MySQL, MySQLi, and PDO.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Calling Stored Procedures Using MySQLi

The MySQLi (MySQL Improved) extension lets you access functionality provided by MySQL 4.1 and above. Note that the MySQLi extension (php_mysqli.dll) DLL is not enabled by default, so you must enable it in php.ini. To learn more about MySQLi extension functions visit this link.

The following example uses the procedures proc() and total_price() shown earlier. You call a stored procedure using MySQLi extension using this syntax:

$mysqli->query( 'CALL proc()' );

The following PHP script example calls the proc() stored procedure and outputs the result:

<?php //Connecting to the books database $mysqli = new mysqli('localhost', 'root', '', 'books'); //Calling the proc() procedure $rs = $mysqli->query( 'CALL proc()' ); while($row = $rs->fetch_object()) { print_r($row); echo "<br />"; } ?>

The output is :

stdClass Object ( [id] => 1 [book] => Introduction to PHP [author] => Mark User [isbn] => 3334-4424-334-3433 [price] => 500 ) stdClass Object ( [id] => 2 [book] => DHTML and CSS [author] => Teague Sanders [isbn] => 4545-23-23-23-23232 [price] => 1500 ) stdClass Object ( [id] => 3 [book] => Introduction to PHP [author] => Weeling Tom [isbn] => 4334-2323-23233-434 [price] => 300 ) stdClass Object ( [id] => 4 [book] => Web design [author] => Weeling Tom [isbn] => 4334-2323-23233-434 [price] => 600 ) stdClass Object ( [id] => 5 [book] => PHP 5 [author] => Weeling Tom [isbn] => 444-87-67665-678678 [price] => 600 ) stdClass Object ( [id] => 6 [book] => JavaServer Pages [author] => Tick Own [isbn] => 897-9898-987-099 [price] => 800 )

Similarly, this example calls the total_price() stored procedure and outputs the result:

<?php $mysqli = new mysqli('localhost', 'root', '', 'books'); $rs = $mysqli->query( 'CALL total_price(@t)' ); $rs = $mysqli->query( 'SELECT @t' ); while($row = $rs->fetch_object()) { print_r($row); echo "<br />"; } ?>

In this case, the output is:

The total price : stdClass Object ( [@t] => 4300 )

Calling Stored Procedures Using PDO

The PDO documentation states: "The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. You do not perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server."

PDO extension gives you the flexibility to change the database backend without altering your access methods. PDO can provide advantages: you can use the same data-access methods regardless of the MySQL version. This does away with the need for using the standard MySQL extension with older versions of MySQL and using the MySQLi extension for later versions. An additional advantage is the ability to use object-oriented code regardless of the version of MySQL. So, when you need flexibility of a database abstraction layer, PDO will make your code much more solid and portable and when you want that application to work faster use one of these two interfaces MySQL or MySQLi.

To use this extension, add the following two lines to php.ini:

extension=php_pdo.dll extension=php_pdo_mysql.dll

The second line represents the MySQL driver, which must be present for the extension to work. Here's an example that calls the total_price stored procedure.

<?php $pdo = new PDO('mysql:dbname=books;host=', 'root', ''); print 'PDO: simple select'; $pdo->query( 'CALL total_price(@t)' ); foreach($pdo->query( 'SELECT @t' ) as $row) { print_r($row); } ?>

The output is:

PDO: simple select Array ( [@t] => 4300 [0] => 4300 )

You can easily write procedures that select single rows. For example, the following procedure selects the bookstore table record for which the ID is 3:

CREATE PROCEDURE proc_new () BEGIN SELECT * from bookstore where id=3; END

Here's an example that calls proc_new() and displays the result:

<?php //Connecting to MySQL server and to the books database $pdo = new PDO('mysql:dbname=books;host=', 'root', ''); print 'Calling the proc_new() stored procedure using PDO'; foreach($pdo->query( 'CALL proc_new()' ) as $row) { print_r($row); } ?>

The output is:

Calling the proc_new() stored procedure using PDO Array ( [id] => 3 [0] => 3 [book] => Introduction to PHP [1] => Introduction to PHP [author] => Weeling Tom [2] => Weeling Tom [isbn] => 4334-2323-23233-434 [3] => 4334-2323-23233-434 [price] => 300 [4] => 300 )

In this article, you’ve seen how to create simple stored procedures and functions, and how to call them from PHP using the MySQL, MySQLi, and PDO database extensions. With these in your toolbox, you can simplify and speed up your code while simultaneously helping to insulate your applications from back-end database changes—all while reducing errors.

Octavia Andreea Anghel is a senior PHP developer currently working as a primary trainer for programming teams that participate at national and international software-development contests. She consults on developing educational projects at a national level. She is a coauthor of the book "XML Technologies—XML in Java" (Albastra, ISBN 978-973-650-210-1), for which she wrote the XML portions. In addition to PHP and XML, she's interested in software architecture, web services, UML, and high-performance unit tests.
Thanks for your registration, follow us on our social networks to keep up-to-date