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

Posted: October 1, 2009 in MYSQL

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

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

Advertisements
Comments
  1. Marc Shaw says:

    Hey, I read a lot of blogs on a daily basis and for the most part, people lack substance but, I just wanted to make a quick comment to say GREAT blog!…..I”ll be checking in on a regularly now….Keep up the good work! 🙂

    – Marc Shaw

  2. JacksonFive says:

    Many reports show that acai berry pulp has a really high antioxidant capacity
    with even more antioxidant effectiveness than cranberry, raspberry, blackberry, strawberry, or blueberry. Acai Force Max

  3. good evening friends

    I last week stumbled into a website. they are listing some discounted italian clothes. the online shop is selling the goods with nearly 65% discounts. my wife really liked to get a pair before the weekend but not confident that order is going to be brought in right on time. I am thinking to get those prada shoes but not sure yet.

    just urged to share with you dudes.

    thnx ppl.

  4. AKfour seven says:

    I stand here today humbled by the task before dofus kamas, grateful for the trust you have bestowed, mindful of the sacrifices borne by our cheap dofus kamas. I thank President dofus power leveling for his service to buy dofus kamas, as well as the generosity and cooperation he has shown throughout this transition.

  5. I tend not to make comments on websites, but your thread was really stimulating and encouraged myself to do tons of research on Acai supplements. I discovered that if taken the right way, Acai Supplements will likely have a healthy effect on your colon and digestive system.

  6. malarvizhik says:

    Hi Raja,

    Nice Article. Keep post your thought
    🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s