With the release of PHP 5.5.0 the mysql_* functions have become deprecated and as such connecting to a MySQL database with PHP 5.5 and up will require the use of either PHP Data Objects (PDO) or an individual MySQL driver (MySQLi). Throughout this tutorial we will focus on utilizing PDO to connect, access, and disconnect from a MySQL database. If you’re hosting provider is running an older version of PHP than 5.5 please refer to our older tutorial about connecting to a MySQL database.

To establish a connection to a MySQL server, specify a data source name (DSN) containing connection parameters, and optionally the username and password of the MySQL account to use. For MySQL, the DSN is a string that indicates the database driver (mysql), and optionally the hostname where the server is running and the name of the database to use. Typical syntax for the DSN looks like this:

mysql:host=host_name;dbname=db_name

The default host is localhost. If dbname is omitted, no default database is selected. The MySQL driver also recognizes port and unix_socket parameters, which specify the TCP/IP port number and Unix socket file pathname, respectively. If you use unix_socket, do not specify host or port. For other database engines, the driver name is different (for example, pgsql for PostgreSQL) and the parameters following the colon might be different as well.

To connect to the MySQL server on the local host to access a database called test with a username and password of testuser and testpass, the connection sequence looks like this:

$dbh = new PDO("mysql:host=localhost;dbname=test", "testuser", "testpass");

When you invoke the new PDO() constructor method to connect to your database server, PDO determines from the DSN which type of database engine you want to use and accesses the low-level driver appropriate for that engine. This is similar to the way that Perl or Ruby DBI scripts reference only the top-level DBI module; the connect() method provided by the top-level module looks at the DSN and determines which particular lower-level driver to use.

If new PDO() fails, PHP throws an exception. Otherwise, the constructor method returns an object of the PDO class. This object is a database handle that you use for interacting with the database server until you close the connection.

When you’re done using the connection, close it by setting the database handle to NULL:

$dbh = NULL;

After that, $dbh becomes invalid as a database handle and can no longer be used as such. If you do not close the connection explicitly, PHP does so when the script terminates.

Published by Michael Boguslavskiy

Michael Boguslavskiy is a full-stack developer & online presence consultant based out of New York City. He's been offering freelance marketing & development services for over a decade. He currently manages Rapid Purple - and online webmaster resources center; and Media Explode - a full service marketing agency.

Join the Conversation

1 Comment

Leave a comment

Your email address will not be published. Required fields are marked *