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:
The default host is localhost. If dbname is omitted, no default database is selected. The MySQL driver also recognizes
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.
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;
$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.