MySQL Stored Procedure Programming

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