I'm not afraid to admit the fact that I am an 80s music fan. A freak. A fanatic. I'm also a programmer, so, as you may surmise, I spend a lot of time in front of my computer listening to, well, 80s music. Do you remember the song by Dire Straits titled "I Want My P-H-P"? Or how about Jimmy Buffett's "Cheeseburger in PHP Paradise"?
OK, maybe I've mixed up my love for music with my other true love, PHP. However, this crush isn't without merit; PHP is one of the sexiest technologies to hit the web, and the recent release of version 4.0 proves that it's getting better with age.
So let me introduce you to PHP, and elaborate upon one of its finest features, database support. Specifically, I will focus on how well it integrates with an open source favorite in the database world: MySQL.
MySQL and PHP have developed a special sort of relationship over the past few years, perhaps due in large part to the fact that they are both freely available for download. The increased popularity of this dynamic duo, coupled with boundless enthusiasm from core developers of both technologies, culminated in a "meeting of the minds" in Israel earlier this year. This resulted in the MySQL library being packaged with the PHP 4.0 distribution, in addition to an agreement to help each other improve the performance quality of product integration whenever the opportunity arises.
PHP 4.0 is perhaps best defined as a server-side web scripting language. It can be integrated directly alongside other web-based languages, such as HTML and WML, enabling the programmer to introduce dynamic content directly to their web applications.
Let's consider a few different ways in which PHP can be displayed within a browser:
Example 1 PHP alone:
<? print "PHP is cool."; ?>
Example 2 PHP embedded in HTML code:
<? print "PHP is"; ?> cool.
Example 3 An even cooler example:
Today's date is: <? print (date ("M d, Y")); ?>
While the ability to easily embed dynamic code into your web application is cool, this is just the beginning of what PHP has to offer. The ability to make mathematical calculations, parse strings, manipulate databases, and do much more via the web browser is easy given the hundreds of predefined PHP functions. PHP 4.0 further expands upon this vast functionality, offering several new features which greatly increase the overall appeal of the language.
It is worth noting that the PHP 4.0 parsing engine is a complete rewrite of its predecessor. Although there are many features of the language worth noting, here are a few of my favorite "standouts" in the new version.
Script interpretation vs. compilation: The incredible popularity of PHP inspired many developers to create large, complex applications using the scripting language, something that was rather unexpected given its humble origins. (Read more about PHP's creator and its history at the Zend Hall of Fame.) Although these attempts were wildly successful, the PHP team knew they could do better.
The main obstacle to overcome was the fact that previous PHP scripts were interpreted line-by-line upon each execution. While this worked just fine for relatively small scripts, it was not the best solution for large and complex scripts, particularly when loops or function calls were involved. As a result, scripts are now entirely compiled before they are executed, eliminating repetitive interpretation and ultimately resulting in substantially faster execution times.
Web server flexibility: PHP had been, quite logically, developed for use in conjunction with the popular Apache web server. However, as time progressed, several other web servers popped up that proved to be quite popular in their own right, such as the Netscape Enterprise server, the Microsoft IIs server, and Zeus. Have no fear, as PHP 4.0 is now compliant to all of these servers, and then some.
Session management: Version 4.0 provides a great new feature that frees the developer from having to create and maintain session IDs. Several protocols are available that eliminate sole reliance on cookies for maintaining session data. For example, if the user turns cookies off, session data can be passed via the URL. Furthermore, session data can be stored via a variety of media, including plaintext files or databases.
Database support: Although there were no huge steps forward in the already marvelous database support in the 4.0 release per se, the packaging of the MySQL Library greatly increased the value of the PHP distribution. Let's discuss the MySQL package in further detail, and highlight PHP/MySQL implementation with a few examples.
MySQL is an extremely fast, robust SQL database server, developed by Michael "Monty" Widenius of T.c.X DataKonsultAB. This database server has gained quite a following in PHP circles, due in large part to the ease with which a MySQL database can be interfaced with the web via PHP's predefined function set. MySQL has been chosen by such companies as Silicon Graphics and the Department of The Navy for internal applications, not to mention being a part of popular web sites such as Yahoo, Slashdot, and Linux.com. Needless to say, organizations such as those mentioned handle colossal amounts of data, lending proof of MySQL's reliability and robustness.
Although a complete summary of MySQL is out of the scope of this particular article, John Paul Ashenfelter's companion piece, YourSQL Database Might Just Be MySQL, offers an insightful introduction to this popular open source database.
At this point, you should be familiar with at least what PHP and MySQL are. Now let's focus our attention on how they interact to build dynamic web applications.
As stated previously, one of the main attractions of PHP is the vast support it provides for various database servers. This convention is certainly the case for the MySQL database server as well, with over thirty predefined functions. Although it is not my intention to cover each function, several of the more applicable scenarios will be covered in detail. Those readers interested in learning more about all of the functions can check out the PHP documentation.
Of course, one of the most important functions is the one that actually opens the connection to the MySQL database server.
mysql_pconnect(host, username, password);
mysql_pconnect() opens a persistent connection to the MySQL server. Essentially, a persistent connection saves valuable system resources, as it will first check to see if a connection already exists. If one does, that connection is used. Otherwise, a new connection is established.
Each of the input parameters is optional and will be supplied in accordance with how MySQL's 'mysql' database has been configured. Essentially, the 'mysql' database controls who can connect to a particular database and from where. For more information about the 'mysql' database, check out the MySQL documentation.
Example 4 Connecting to a mysql database:
<? $host = "www.oreillynet.com"; $username = "mickey_mouse"; $password = "cheese"; $new_connection = mysql_pconnect ($host, $username, $password); if (! $new_connection) : print "Connection failed!"; endif; ?>
Of course, you could also open links to several MySQL servers simultaneously. Just assign a different
link_identifier to each connection. (In the above example,
$new_connection is the
Once a database connection has been affirmed, a database must be selected from which queries can be perfomed. This is done, conveniently enough, through a function entitled
mysql_select_db($database_name [, link_identifier]);
link_identifier is only necessary when connections to multiple MySQL servers have been established. Of course, a valid database name must be given.
Example 5 Selecting a database:
<? // . . . establish MySQL server connection $database_name = "automobile_specs"; $auto_db = mysql_select_db($database_name); if (! $auto_db) : print "Could not select the database $database_name!"; endif; ?>
So, we've established a connection to the MySQL server and selected a database to query. Next in line is executing the actual queries that will mine and manipulate the database tables. Let's assume that we have a table within the database
favorite_cars. The table was created as follows:
mysql>create table favorite_cars model char(15), horsepower INT, color char(6) );
This leaves us with three columns of information to manipulate.
All queries are handled via PHP's
mysql_query(query [, link_identifier]);
query parameter specifies the actual query that you would like to perform on the MySQL table. As was the case with previous commands, the
link_identifier is optional, used when there are multiple server connections open.
Let's demonstrate the
mysql_query() function by inserting some data into the
Example 6a Insertion of data:
<? // . . . establish MySQL server connection // . . . select database $table = "favorite_cars"; $query = "insert into $table values ("Ferrari", "155", "red")"; $result = mysql_query($query); if (! $result) : print "Could not insert data!"; endif; ?>
Whether or not you specify the query statement within a separate variable as I have done is simply a matter of style preference. You could just have easily done the following:
Example 6b Alternative usage of
<? . . . $result = mysql_query("insert into $table values ("Ferrari", "155", "red")"); . . . ?>
Just as we have used
mysql_query() to insert data, the same function would be used to mine data from the table.
Example 7 Mining data from a MySQL table:
<? . . . $query = "select * from $table where model = "Ferrari""; $result = mysql_query($query); . . . ?>
Many of you may be wondering where the data went when we queried the table in the above example. For the sake of simplicity, it is safe to assume that any results are sitting within the variable
$result. However, in order to display the data to the browser screen, we need to employ another MySQL function entitled
mysql_fetch_array(), in addition to making use of some basic PHP programming concepts.
mysql_fetch_array(result [, result_type]);
The result parameter is just the result returned by the
Example 8 Displaying data to the browser:
<? // . . . establish MySQL server connection // . . . select database $query = "select * from $table where model = "Ferrari""; $result = mysql_query($query); while ($row = mysql_fetch_array($result)) : print $row["model"]; print "<BR>"; print $row["horsepower"]; print "<BR>"; print $row["color"]; print "<BR>"; endwhile; ?>
First inserting data into the 'favorite_cars' table as was done in example 6, and then executing example 8, the following would be displayed to the browser:
Ferrari 155 red
Although there are other functions used to facilitate the display of data returned from a query, such as
mysql_result(), I personally find
mysql_fetch_array to be the easiest to use. In terms of performance, both functions are equally fast. Again, it's more a matter of style than anything else.
Well, I'm not sure that I'd go that far, but you can see the advantages of using PHP and MySQL together. They're each powerful in their own right, they're compatible together, and they're open source. They might not be a match made in heaven, but I definitely think we're looking at a long-term relationship here.
W.J. Gilmore has been developing PHP applications since 1997, and is frequently published on the subject within some of the Web's most popular development sites. He is the author of 'A Programmer's Introduction to PHP 4.0' (January 2001, Apress), and is the Assistant Editorial Director of Web and Open Source Technologies at Apress.
Discuss this article in the O'Reilly Network Forum.
Return to the O'Reilly Network Hub.
Copyright © 2009 O'Reilly Media, Inc.