Access a MySQL Database Using PDO

by

This tutorial will show you how to access a mysql database using the PHP Data Objects interface, also known simply as PDO. One big advantage of using PDO versus other methods is the use of prepared statements which offers much better security than the mysql or mysqli libraries. Also PDO can connect to several different database systems by specifying another driver. It is almost as easy as switching the driver to change from one database system to another (except for DB specific SQL syntax).

This tutorial requires that you already have a mysql database to connect to or know how to create a mysql database and will set one up. If you do not know how to setup a MySQL database then you will want to check out some of our other tutorials.

As I mentioned above PDO supports multiple database systems. If you would like to see which drivers you have available on your server you can print the array of drivers using:

print_r(PDO::getAvailableDrivers());

To connect to a MySQL database using PDO you simply need to create a new PDO object and provide the database information:

//Database Credentials
$host = 'localhost';
$database = 'DatabaseName';
$username = 'dbuser';
$password = 'dbpassword';

try {
  $DBH = new PDO("mysql:host=$host;dbname=$database", $username, $password);

}
catch(PDOException $e) {
    echo $e->getMessage();
}

echo "it works";

In the sample above you need to specify the correct information to connect to your database. The $host variable is the system that the database is hosted on. If the database is on the same server that you are executing PHP on then you can simply use localhost. The $database variable is the name of the specific database on the server. $username and $password are just that, the username and password of the database user.

Save the page with a .php extension and execute it. If the database connection works properly than you should simply see a message that says “it works” if not you will see some sort of error that should point you in the right direction to start debugging. Common errors are: wrong user/pass, not allowing a user to connect from other hosts, and syntax errors. If you are having trouble getting this connection to work feel free to ask in the comments.

Now that you have the database connection what do you do with it? I will show you that in future posts.