Using MySQL Stored Programs with PHP

The combination of PHP and MySQL is one of the most popular and powerful partnerships in open source web development and is a key component of the LAMP (Linux-Apache-MySQL-PHP/Perl/Python) stack. There are reportedly more than 18 million web sites based on PHP technology (according to http://www.netcraft.com), and the majority of these are using MySQL as the underlying database.

PHP started off as a simple CGI-based processor for amateur web development in the mid-1990s. It borrowed heavily from the Perl language (at the time, the most popular approach for CGI-based dynamic web development), but was more tightly integrated with HTML andunlike Perlwas designed specifically for web development.

PHP takes a similar approach to dynamic web content as Microsoft's ASP (Active Server Pages) and J2EE's JSP (Java 2 Enterprise Edition Java Server Pages). All of these technologies involve embedding tags into HTML pages (renamed appropriately as PHP, ASP, or JSP pages, of course) that control the dynamic content of the page. In the case of PHP, the tags contain PHP code. The PHP code is executed by the PHP engine, which is usually deployed within the web server (Apache, IIS, etc.) and typically interacts with a database to provide dynamic, data-driven content.

As a language, PHP delivers much of the flexibility and power of the popular Perl open source scripting language and has a wide variety of interfaces to back-end databases. It is probably fair to characterize PHP as having a shallower learning curve than the ASP.NET or J2EE alternatives. Also, since PHP is open source, software licensing costs are, of course, minimal (although many larger enterprises seek support from Zend Corporation or another commercial entity).

In this chapter we will review the use of PHP with MySQL and show how stored programs can be used within PHP-based applications.