I’ll demonstrate a simple example on how to connect to MySQL using PHP’s PDO class. Just some of the benefits of PDO are that it’s fast and if you use the PDO::prepare() method, you’ll avoid SQL injection attacks by calling the PDO::quote() method. The other advantage is that it supports multiple databases. So let’s dive right into the code:

$hostname = ‘localhost’;

$user = ‘your_username’;

$password = ‘your_password’;

to try {

$db = new PDO(“mysql:host=$hostname;dbname=mysql”, $user, $password);

echo ‘Connected to database’;

}

catch(PDOException $e) {

echo $e->getMessage();

}Fatal error new PDO instance

Just an important note that if you get the following type of fatal error in your development environment:

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000] [1049] Unknown database ”user” at C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1classesstd.pdo_singleton.class.inc:30 Stack Trace : # 0 C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1classesstd.pdo_singleton.class.inc(30): PDO->__construct(‘mysql :host= loca…’, ‘username’, ‘password’) #1 C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1classes std.mysql.class_test. inc(43): db::getConnect() #2 C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1connect.php(6): MySqlDb->confirmUserPass (‘usertest’, ‘passtest’) #3 {main} released in C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1classesstd.pdo_singleton.class. Inc on line 30

It looks pretty messy and hard to figure out. When trying to figure out the error code, I usually look at the first error which led me to see why it was reporting an “Unknown Database” when it existed. The extra quotes also gave me a clue to the problem. So I concluded that the problem resulted in putting extra quotes around the host and/or dbname values. The following will generate the above error:

$db = new PDO(“mysql:host=’localhost’;dbname=’mysql'”, $username, $password);

So if you don’t use variables, don’t add single quotes around the host and dbname values. In other words, use the following code instead:

$db = new PDO(“mysql:host=localhost;dbname=mysql”, $user, $password);

This is a simple test to connect to your mysql database or any other supporting database. Just FYI, if you want to connect to PostgreSQL, which is another popular and robust database, use the following code instead of the instantiation line:

$db = new PDO(“pgsql:dbname=pdo;host=localhost”, “username”, “password”);

If you are in a development environment and want to display your errors directly on the screen, you can specify the errors that are displayed. You must set the display_errors setting to ‘on’ in your php.ini file. So set your error attributes after instantiating the PDO class like this:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

There are three types of error reporting settings for PDO::ATTR_ERRMODE:

PDO::ERRMODE_SILENT = error codes

PDO::ERRMODE_WARNING = E_WARNING

PDO::ERRMODE_EXCEPTION = Throw exceptions

Here is an example implementation of PDO::ATR_ERRMODE:

$hostname = ‘localhost’;

$user = ‘your_username’;

$password = ‘your_password’;

to try {

$db = new PDO(“mysql:host=$hostname;dbname=articles”, $user, $password);

echo ‘Connected to database’; // check the connection

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$sql = ‘Select * from tutorialref where id=1’;

$result = $db->query($sql);

foreach ($result as $row) {

echo $row[‘id’] .’ -‘. $row[‘author’] . ”;

}

$db = null; // close the connection to the database

}

catch(PDOException $e) {

echo $e->getMessage();

}

Don’t confuse this with errors generated by the error_reporting php setting. PDO::ATTR_ERRMODE errors apply to the sql query and its results. I’ll dive into the error settings and the different outputs of the php.ini error reporting settings and the PDO::ATTR_ERRMODE reporting settings in a future article.