Executing Stored Procedures and Functions From PHP in Windows (cont’d)

Calling Stored Procedures from PHP
To call MySQL stored procedures and functions from PHP, you need the following database extensions:

• MySQL
• MySQLi
• PDO

After installing those extensions, you’ll be able to call MySQL stored procedures and functions from PHP. As mentioned earlier, stored procedures and functions in MySQL are associated with a specific database. The examples in this section use a books database created using this SQL statement:

create table bookstore
(id int not null auto_increment primary key,
book varchar(50),
author varchar(50),
isbn varchar(50),
price int);

The SQL statements used to populate the bookstore table from Figure 1 are:

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

Figure 1. Bookstore Table: The figure shows the table contents and structure from the books database

Figure 1 shows the table bookstore structure and some sample content.
Call Stored Procedures Using the MySQL Database Extension

The MySQL database extension gives you access to the MySQL database server. You install the php_mysql.dll like any other extension. You can find more information about the MySQL functions here.

First, you need a simple stored procedure. This one, called proc, selects all the fields in the bookstore table created earlier.

CREATE PROCEDURE proc ( )
BEGIN
SELECT * from bookstore;
END

The following PHP script connects to the MySQL server, selects the books database, calls the proc stored procedure, which has no arguments, and outputs the result:

<?php
//Create the connecting to MySQL
$con = mysql_connect('localhost','root','',false,65536);
mysql_select_db('books');

//Call the proc() procedure
$result= mysql_query("CALL proc();")
or die(mysql_error());

//Output the result
while($row = mysql_fetch_row($result))
{
for($i=0;$i<=6;$i++){
echo $row[$i]."
“;
}
echo “—”;
}
//Close the connection
mysql_close($con);
?>

Author’s Note: Using the syntax $con = mysql_connect(‘localhost’,'root’,”); will not work, because to return a result set from a stored procedure to PHP, you must use either the multiple-statements connect option or the multiple-results option (or both). If the routine does not return a result set, neither option is required.

The output is:

1—Introduction to PHP—Mark User—3334-4424-334-3433—500——–
2—DHTML and CSS—Teague Sanders—4545-23-23-23-23232—1500——-
3—Introduction to PHP—Weeling Tom—4334-2323-23233-434—300—–
4—Web design—Weeling Tom—4334-2323-23233-434—600———
5—PHP 5—Weeling Tom—444-87-67665-678678—600———
6—JavaServer Pages—Tick Own—897-9898-987-099—800———

Here’s a procedure example, named total_price, calculates the total of the price field from the bookstore table. It uses an OUT parameter to hold the total:

CREATE PROCEDURE total_price ( OUT total int)
BEGIN
SELECT sum(price) into total from bookstore;
END

The following PHP script calls the total_price procedure and displays the result using the OUT parameter total, which is an int:

The output is:

The total price is = 4300

Calling Stored Functions Using the MySQL Extension

To illustrate making stored function calls here’s a simple stored function:

CREATE FUNCTION simple_operation (price int) RETURNS int(11)
RETURN price*1000

The simple_operation function takes an integer argument, makes a simple calculation and returns an integer.

The output is:

The total price is = 5000

Executing Stored Procedures and Functions From PHP in Windows

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

Stored procedures and functions are a new feature of MySQL 5.0. A stored procedure is a pre-built procedure containing one or more SQL statements stored in the database server.

GET THE CODE

This article shows how to create a few basic stored procedure and function examples, and call MySQL stored procedures and functions from PHP with the help of some database extensions.

Advantages of Using Stored Procedures
Stored procedures can provide improved performance because they can be precompiled, and because the client needs to send only a name and required parameters to the server to run a stored procedure rather than having to send the entire procedure code. In addition, stored procedures provide these other advantages:

* They simplify complex operations by encapsulating processes into a single easy-to-use unit.
* They help avoid errors because you can use a single well-tested stored procedure in many applications.
* A stored procedure runs the same way from all languages/environments. Because stored procedures reside on the database server, it makes no difference what application environment you use to call them—the stored procedure itself remains consistent.
* They reduce the risk of data damage by limiting access to the data.
* They can reduce network traffic. Complex, repetitive tasks may require getting some data, applying some logic to the retrieved values, and using the results to retrieve more data. When this multi-step process takes place completely on the database server, as in a stored procedure, it can eliminate the need to send result sets and new queries back and forth from to the database server.

Creating Stored Procedures in MySQL

MySQL 5.0 finally introduces functionality for stored procedures. In this implementation, each stored procedure or function is associated with a particular database, which has the following implications:

* When you call a stored procedure or function, the database issues an implicit USE db_name command, which remains in effect until the stored procedure terminates.
* You can create a stored procedure name for a given database name only if that name is unique in the current database. For example, to invoke a stored procedure named proc or function named func associated with the book database, you can write CALL book.proc() or CALL book.func().
* When you drop a database, MySQL drops all stored procedures and functions associated with that database as well.

Defining procedures or functions is a two-step process:

1. Define the name of the procedure or function, and set its parameters.
2. Define the body of the procedure or function between BEGIN and END statements.

Here’s the basic syntax:

CREATE PROCEDURE procedure_name ([procedure_parameter[,...]])
routine_body

The procedure_parameter is a list of parameters and their directions, composed using the following arguments:

* IN: Passes a value into a procedure. The procedure can modify the value, but the modification is not visible to the caller when the procedure returns.
* OUT: Passes a value from the procedure back to the caller. The parameter’s initial value in the procedure is NULL; the procedure usually changes that value, and the final value is visible to the caller when the procedure returns.
* INOUT: The caller initializes an INOUT parameter, but the procedure can modify the value, and the final value is visible to the caller when the procedure returns.

Calling a Stored Procedure in MySQL
Within MySQL, you call a stored procedure using the call method, for example:

call books.proc(@a);
select @a;

Creating Stored Functions in MySQL
There are a few key differences between creating a stored procedure and creating a function:

* The keyword function replaces the procedure keyword.
* You don’t need to specify parameter direction, because all parameters are IN.
* The RETURNS keyword after the parameter list specifies the return value type.
* You don’t need to use a BEGIN…END block.
* To call a function, use the syntax select function(parameter_list).

Here’s the function creation syntax:

CREATE FUNCTION function_name ([function_parameter[,...]])
RETURNS type
routine_body

Here’s a simple stored function example that calculates and returns an int:

CREATE FUNCTION simple_operation (price int) RETURNS int
RETURN price*1000

To call it from SQL Server, use:

SELECT simple_operation(5)

The return value in this case is 5000.

Download the SQL scripts and sample PHP code for this article.