PHP PDO MySQL – Simple Example Connecting to MySQL With PDO Class

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

$hostname = ‘localhost’;

$username = ‘your_username’;

$password = ‘your_password’;

try {

$db = new PDO(“mysql:great number=$hostname;dbname=mysql”, $username, $password);

echo ‘Connected to database’;

}

catch(PDOException $e) {

echo $e->getMessage();

}Fatal Error new PDO example

Just an important observe that if you receive the following kind of fatal error in your development ecosystem:

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000] [1049] Unknown database ”user”’ in 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:great number=loca…’, ‘username’, ‘password’) #1 C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1classesstd.mysql.class_test.inc(43): db::getConnect() #2 C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1connect.php(6): MySqlDb->confirmUserPass(‘usertest’, ‘passtest’) #3 {main} thrown in C:Program FilesApache Software FoundationApache2.2htdocstesttrunkcodelogin1classesstd.pdo_singleton.class.inc on line 30

Looks pretty messy and hard to decipher. When trying to decipher error code I usually look at the first error which led me to see why it was reporting an “Unknown database” when it was existing. The additional quotes also gave me a hint as to the problem. So I concluded the problem resulted in the placement of additional quotes around the values of great number and/or dbname. The following will generate the past error:

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

So if you do not use variables then do not add the single quotes for the values of great number and dbname. In other words, use the following code instead:

$db = new PDO(“mysql:great number=localhost;dbname=mysql”, $username, $password);

This is a simple test to connect to your mysql database or any other sustain database. Just for your information if you wish to connect to PostgreSQL which is another popular and strong database use the following code in place of the line of instantiation:

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

If you’re in a development ecosystem and wish to characterize your errors directly to the screen you can specify the errors displayed. You need to set your display_errors settings to ‘on’ in your php.ini file. So set your error attributes after instantiating the PDO class like so:

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

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

PDO::ERRMODE_SILENT = error codes

PDO::ERRMODE_WARNING = E_WARNING

PDO::ERRMODE_EXCEPTION = Throw exceptions

Here’s an example of implementing PDO::ATR_ERRMODE:

$hostname = ‘localhost’;

$username = ‘your_username’;

$password = ‘your_password’;

try {

$db = new PDO(“mysql:great number=$hostname;dbname=articles”, $username, $password);

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

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

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

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

foreach ($consequence as $row) {

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

}

$db = null; // close the database connection

}

catch(PDOException $e) {

echo $e->getMessage();

}

Don’t confuse this with the errors generated from the php setting of error_reporting. The errors from PDO::ATTR_ERRMODE 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 PDO::ATTR_ERRMODE report settings in a future article.

Leave a Reply