Tutorial Ref.com

PHP PDO MySQL:Example How to Connect to MySQL Using PDO Class, PDO Error Attributes

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 support. So let's dive right into the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
$hostname = "localhost";
$username = "your_username";
$password = "your_password";
 
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
    echo "Connected to database"; // check for connection
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

Fatal Error new PDO Instance

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

1
2
3
<?php
Fatal error: Uncaught exception "PDOException" with message "SQLSTATE[42000] [1049] Unknown database ""user""" in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\trunk\code\login\1\classes\std.pdo_singleton.class.inc:30 Stack trace: #0 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\trunk\code\login\1\classes\std.pdo_singleton.class.inc(30): PDO->__construct("mysql:host=loca...", "howtopr1_vkim", "htmys2007") #1 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\trunk\code\login\1\classes\std.mysql.class_test.inc(43): db::getConnect() #2 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\trunk\code\login\1\connect.php(6): MySqlDb->confirmUserPass("usertest", "passtest") #3 {main} thrown in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\trunk\code\login\1\classes\std.pdo_singleton.class.inc on line 30
?>

This a result of placing extra quotes around the values of host and/or dbname. The following will generate the previous error:

1
2
3
<?php
$dbh = new PDO("mysql:host="localhost";dbname="mysql"", $username, $password);
?>

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

1
2
3
<?php
$dbh = new PDO("mysql:host=localhost;dbname=mysql", $username, $password);
?>

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

1
2
3
<?php
$db = new PDO("pgsql:dbname=pdo;host=localhost", "username", "password" );
?>

If you're in a development environment and wish to display 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:

1
2
3
<?php
$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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$hostname = "localhost";
$username = "howtopr1_vkim";
$password = "htmys2007";
 
try {
    $db = new PDO("mysql:host=$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";
    $result = $db->query($sql);
    foreach ($result as $row) {
        echo $row["id"] ." - ". $row["author"] . "<br />";
    }
 
    $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.

TutorialRef.com - tutorials, guides, how-tos, helps