The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

How do I access a MySQL database?

Connecting to MySQL with PHP couldn't be easier. It's essentially a two–step process; first connect to the MySQL database server itself, then inform MySQL of the database you want to connect to.

A Basic Connection

Here is a MySQL database connection in its simplest form:

Example 3.2. 1.php  
 
<?php  
$host   = 'localhost'; // Hostname of MySQL server  
$dbUser = 'harryf';    // Username for MySQL  
$dbPass = 'secret';    // Password for user  
$dbName = 'sitepoint'; // Database name  
 
// Make connection to MySQL server  
if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {  
 die('Could not connect to server');  
}  
 
// Select the database  
if (!mysql_select_db($dbName, $dbConn)) {  
 die('Could not select database');  
}  
 
echo 'Connection successful!';  
// ... some code here using MySQL  
 
// Close the connection when finished  
mysql_close($dbConn);  
?>

It's important to remember that MySQL is a separate server program, much like Apache. Both servers may run on the same physical computer (hence our use of $host = 'localhost'; in the above example) but it's also possible to connect to MySQL on a remote computer, for example $host = 'anothercomputer.com';. To make matters a little more interesting, MySQL also has its own port number, which by default is 3306. PHP assumes that 3306 will be the port number but should you need to use a different one, all you need is $host = 'anothercomputer.com:4321';.

The other conceptual hurdle lies in understanding that a single MySQL server may provide access to many databases, which is why you need to select your database in PHP after connecting to the server.

Returning to the code above, there are a few things to note. First, I've placed in variables the values I need in order to connect to MySQL. This simply makes our lives easier; it's common to store this kind of information in separate files that are included in every PHP script, making it possible to change many scripts at one time. We'll be looking at further tricks we can employ to make life easier in a moment.

The mysql_connect function does the work of connecting to a MySQL server. The value it returns is either a link identifier (a value supplied by PHP to identify the connection), or FALSE, meaning the connection failed.

if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {  
 die('Could not connect to server');  
}

This if statement asks the question "Did I successfully connect to the MySQL server?" If not, it uses die to terminate the script.

Next, we've selected the database we want with mysql_select_db, using the same if statement technique:

if (!mysql_select_db($dbName, $dbConn)) {

Note that we provided the variable containing the link identifier as the second argument to mysql_select_db. We wouldn't usually need to do this (the argument is optional), but when a complex script juggles multiple database connections, this method can help ensure PHP knows which you're referring to.

Finally, we've used mysql_close to disconnect from the server again:

mysql_close($dbConn);

This occurs at the bottom of the script, once we've run some imaginary PHP code that used the connection. Closing the connection is generally optional—PHP automatically closes any connections after the script finishes.

Note that connections made with mysql_pconnect are different. This function establishes a persistent connection to the database to be reused by multiple PHP scripts. Using a persistent connection makes your scripts slightly faster, as PHP no longer has to reconnect each time, but speed comes at a price: if your Website runs on a shared server, persistent connections may monopolize that server, resulting in other sites being unable to connect at times. In such environments, it's typical to either avoid mysql_pconnect, or configure MySQL so that connections are terminated the moment they stop doing anything, using a short connection timeout value.