O'Reilly Network    
 Published on O'Reilly Network (http://www.oreillynet.com/)
 See this if you're having trouble printing code examples


Are PHP and MySQL the Perfect Couple?

by W.J. Gilmore
06/16/2000

Related Articles

YourSQL Database Might Just Be MySQL

How PostgreSQL Rose to Fame

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.

What is PHP?

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.

PHP 4.0 features

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.

What is MySQL?

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.

PHP/MySQL interaction

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.

Connectivity

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

Selecting a database

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();

mysql_select_db($database_name [, link_identifier]);

The 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;

?>

 

Querying the database

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 automobile_specs entitled 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() function:

mysql_query(query [, link_identifier]);

The 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 favorite_cars table.

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 mysql_query():

<?
. . . 

$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 mysql_query().

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.

A heavenly match?

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.