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.
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
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($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; ?>