Calling a Stored Procedure from PHP

We've shown you how to call stored programs from the MySQL command-line client, from the MySQL Query Browser, and from another stored program. In the real world, however, you are more likely to call a stored program from another programming environment, such as PHP, Java, Perl, Python, or .NET. We discuss the details of using stored programs within each of these environments in Chapters Chapter 12 through Chapter 17.

For now, let's look at how you can call a stored procedure (shown in Figure 2-18) from PHP, which is probably the development environment most commonly used in conjunction with MySQL.

When interacting with MySQL from PHP, we can choose between the database-independent PEAR::DB extension, the mysqli (MySQL "improved") extension, and the more recent PHP Data Objects (PDO) extension. In this example we will use the mysqli extension. Chapter 13 describes the details of these extensions.

Figure 2-19 shows PHP code that connects to the MySQL server and calls the stored procedure. We won't step through the code here, but we hope that it will give you a sense of how stored programs can be used in web and other applications.

Figure 2-18. Stored procedure to be called from PHP

The PHP program prompts the user to specify a department ID; it then calls the stored procedure employee_list to retrieve a list of employees that belong to that department. Figure 2-20 shows the output displayed by the PHP/stored procedure example.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208
Similar book on Amazon © 2008-2017.
If you may any questions please contact us: