Module 7: Interfacing with Programs

This module deals with three programs that commonly interface with MySQL: PHP, Microsoft Excel, and Microsoft Access. PHP and MySQL are often jointly used in dynamic web sites, where information is displayed or accepted in a real-time environment. Information stored in Excel and Access is often migrated to MySQL, as users data storage and manipulation needs become more sophisticated and demanding. However, you may need to bring some data into Excel or Access. For example, some users prefer to use Access as a front end to view or manipulate their MySQL-stored data, because they are already familiar with its GUI.

If you are interested in using any of these programs with MySQL data, this module will explain how to do that. We will assume that you already have some familiarity with the program.

Critical Skill 7.1 Use PHP and MySQL

PHP is a scripting language whose most common use is creating dynamic web pages. It is common to see PHP and MySQL working together in web sites and web-based applications. PHP is fairly easy to learn and quick to use, making it one of the fastest growing web languages, and it is now making inroads into larger enterprise applications. This means that the same technology that makes a hobbyist s web site can also be used to power large, business-critical web software.

This skill will focus on how you can use PHP to interface with a MySQL database. At the time of this book, version 5 of PHP is just about ready for production. Therefore, we will also cover the new, improved, MySQL-specific functions found in this new PHP release.

Introducing PHP

PHP stands for Pre-Hypertext Processor. Hypertext , a concept Tim Berners-Lee is credited with creating, is the linking of text documents following protocols and standards. One example of hypertext is the Hypertext Markup Language (HTML). PHP, in conjunction with a web server like Apache or Microsoft Internet Information Services (IIS), allows developers to dynamically alter or produce the markup language (HTML) just prior to sending it to the client (the web browser), where it is rendered as a web page. This allows for the real-time insertion of information as simple as the current date and time or as complex as current database contents.

Uses for PHP

PHP has many uses, most involving some type of markup language and the Internet. For example, PHP can be used in HTML applications where a standard web browser is the intended destination. It can also be used with the Wireless Markup Language (WML) where a wireless device (such as a mobile handset) is the intended destination.

One of the newer developments in web technology is web services, including Simple Object Access Protocol (SOAP) and Remote Procedure Calls using the XML standard (XML-RPC). Web services use the same protocols used for HTML, but some other application is the intended destination, instead of a person s web browser. Typically, you use this for data exchanges between applications or systems. PHP can also be used to create command-line scripts and more traditional client/server programs, using a project called PHP-GTK (for GIMP Tool Kit).

PHP Coding

PHP is a loosely typed language. That means its variables (string, integer, Boolean, and so on) are determined by their context, unless specifically defined. This is important when updating or inserting information into the database, as you will see later in this section. PHP s syntax is similar to Perl s syntax: variables begin with a dollar sign ( $ ) and code segments end with a semicolon ( ; ).

PHP processing instructions are enclosed in < ?php ? > tags, similar to the XML recommendation for processing instructions (< ?xml ? >). (There are other tags and variations for enclosing PHP code, but it is recommended that you use the standard full PHP tags for clarity and consistency in your code.) Comments within PHP code are preceded by double forward slash marks (//).

For more information about PHP basics and syntax, see Appendix C of this book. If you would like to learn about PHP in more detail, visit http://www.php.net or read a PHP-specific book such as PHP 4: A Beginner's Guide by William McCarty (McGraw-Hill/Osborne). Also, you can see what PHP code looks like and how others are using the language by examining coding examples provided by a reputable source. One great resource for high-quality code is PEAR (http://pear.php.net).