Using MySQL

This lesson assumes that you already have MySQL installed on your web server and that PHP has the MySQL module loaded. For information on installing MySQL, see http://dev.mysql.com/doc/mysql/en/Installing.html, and to learn how to activate MySQL support in PHP, refer to Lesson 23, "PHP Configuration."

Further Reading To learn about the MySQL database, read Sams Teach Yourself MySQL in 24 Hours by Julie Meloni. Or for a quick SQL language guide, refer to Sams Teach Yourself SQL in 10 Minutes by Ben Forta.

PHP 5 introduced the mysqli extension, which can take advantage of new functionality in MySQL version 4.1 and higher and can also be used in an object-oriented manner. This book concentrates on the classic mysql extension, because it is still the version offered by many web hosting providers and remains available in PHP 5.

Generally speaking, if you want to use mysqli instead of the classic mysql extension described in this lesson, most function names are prefixed mysqli rather than mysql, but they behave in a similar way. Refer to the online documentation at www.php.net/mysqli for more information.

Connecting to a MySQL Database

You can connect to a MySQL database by using the mysql_connect function. Three arguments define your connection parametersthe hostname, username, and password. In many cases, the MySQL server will be running on the same machine as PHP, so this value is simply localhost. A typical mysql_connect statement may look like the following:

 $db = mysql_connect("localhost", "chris", "mypassword"); 

Database Hostnames Because MySQL uses host-based authentication, you must provide the correct hostnameone that allows a connection to be made. For instance, your MySQL server may be running on www.yourdomain.com but it might only be configured to accept connections to localhost.

Unless you are sure that the MySQL server is running somewhere else, the hostname to use is almost always localhost.

The mysql_connect function returns a database link identifier, which was assigned to $db in the previous example. This resource is used as an argument to the other MySQL functions.

Notice that the connection parameters given to mysql_connect do not include a database name. In fact, selecting the database is a separate step after you are connected to a MySQL server; to do it, you use the mysql_select_db function. For example, the following statement selects mydb as the current database:

 mysql_select_db("mydb", $db); 

Link Identifiers The $db argument is not actually required in mysql_select_db and many other MySQL functions. If it is omitted, PHP assumes that you mean the most recently opened MySQL connection. However, it is good practice to always include the link identifier in MySQL function calls for clarity in your code.

After mysql_select_db has been called, every subsequent SQL statement passed to MySQL will be performed on the selected database.

When you are finished using MySQL in a script, you close the connection and free up its resources by using mysql_close, like this:

 mysql_close($db);