Executing Stored Procedures and Functions From PHP in Windows

Executing Stored Procedures and Functions From PHP in Windows

tored 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.

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.
Author’s Note Specifying a parameter as IN, OUT, or INOUT is valid only for a procedure. In contrast, function parameters are always IN parameters.

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 typeroutine_body

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

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

To call it from SQL Server, use:

SELECT simple_operation(5)

The return value in this case is 5000.

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:

"; }echo "---";}//Close the connectionmysql_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)BEGINSELECT 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

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:

query( 'CALL proc()' );while($row = $rs->fetch_object()){print_r($row);echo "

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:

query( 'CALL total_price(@t)' );$rs = $mysqli->query( 'SELECT @t' );while($row = $rs->fetch_object()){print_r($row);echo "

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:


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.

query( 'CALL total_price(@t)' );foreach($pdo->query( 'SELECT @t' ) as $row){print_r($row);}?>

The output is:

PDO: simple selectArray ( [@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 ()BEGINSELECT * from bookstore where id=3;END

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

query( 'CALL proc_new()' ) as $row){print_r($row);}?>

The output is:

Calling the proc_new() stored procedure using PDOArray ( [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.



Share the Post:
5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions

Bebop Charging Stations

Check Out The New Bebob Battery Charging Stations

Bebob has introduced new 4- and 8-channel battery charging stations primarily aimed at rental companies, providing a convenient solution for clients with a large quantity of batteries. These wall-mountable and

Malyasian Networks

Malaysia’s Dual 5G Network Growth

On Wednesday, Malaysia’s Prime Minister Anwar Ibrahim announced the country’s plan to implement a dual 5G network strategy. This move is designed to achieve a more equitable incorporation of both

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining

Price Wars

Inside Hyundai and Kia’s Price Wars

South Korean automakers Hyundai and Kia are cutting the prices on a number of their electric vehicles (EVs) in response to growing price competition within the South Korean market. Many

Solar Frenzy Surprises

Solar Subsidy in Germany Causes Frenzy

In a shocking turn of events, the German national KfW bank was forced to discontinue its home solar power subsidy program for charging electric vehicles (EVs) after just one day,

Electric Spare

Electric Cars Ditch Spare Tires for Efficiency

Ira Newlander from West Los Angeles is thinking about trading in his old Ford Explorer for a contemporary hybrid or electric vehicle. However, he has observed that the majority of

Solar Geoengineering Impacts

Unraveling Solar Geoengineering’s Hidden Impacts

As we continue to face the repercussions of climate change, scientists and experts seek innovative ways to mitigate its impacts. Solar geoengineering (SG), a technique involving the distribution of aerosols

Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their Razer Blade 17 model. Typically

Innovation Ignition

New Fintech Innovation Ignites Change

The fintech sector continues to attract substantial interest, as demonstrated by a dedicated fintech stage at a recent event featuring panel discussions and informal conversations with industry professionals. The gathering,

Import Easing

Easing Import Rules for Big Tech

India has chosen to ease its proposed restrictions on imports of laptops, tablets, and other IT hardware, allowing manufacturers like Apple Inc., HP Inc., and Dell Technologies Inc. more time