Using PDO in PHP 5 with MYSQL
Installation
You should first refer to the installation guide in PDO Installation.
In my particular case since I was intending to use this with the MySQL database, the configuration options I had to add were:
'--enable-pdo=shared' '--with-pdo-mysql=shared,/var/production/mysql' '--with-sqlite=shared' '--with-pdo-sqlite=shared'
And I also had to add the following lines to my php.ini file:
extension=pdo.so
extension=pdo_mysql.so
Testing the installation
//How to get which drivers are available for PDO
print_r( PDO::getAvailableDrivers() );
which shows
Array ( [0] => mysql )
Starting a PDO Connection
//Starting a PDO Connection
try {
//Please fill this in with appropriate data for your Mysql Database, User and User Password.
$dbh = new PDO('mysql:host=localhost;dbname=test', 'test_user', 'test_user_password');
} catch(PDOException $e) {
echo $e->getMessage();
}
print_r($dbh);
Which shows
PDO Object ( )
You might get an exception there if you don’t set up your data ok, or if there is no driver for your DB engine.
For example if I try the following:
//Starting a PDO Connection
try {
//Sending the wrong Password.
$dbh = new PDO('mysql:host=localhost;dbname=test', 'test_user', 'invalid_password');
} catch(PDOException $e) {
echo $e->getMessage();
}
I get SQLSTATE[28000] [1045] Access denied for user ‘test_user’@'localhost’ (using password: YES)PDO Object ( ), so you should use try catch and write appropriate code for the event of the PDO connection failing.
Simple PDO Operations
For the purpose of this examples I’m setting up the following Table in a DB called Test, and I’m inserting some basic data.
Create database test
create database test;
use test;
CREATE TABLE `test_one` (
`test_oneID` int(10) unsigned NOT NULL,
`test_char` varchar(127) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `test_one` VALUES (1,'aaaa'),(2,'bbbb'),(3,'cccc'),(4,'dddd'),(5,'eeee');
Executing a simple statement
//Preparing the statement
$stm = $dbh->prepare("Select * from test_one");
//Simple execution of the statement
$stm->execute();
print_r( $stm );
Shows PDOStatement Object ( [queryString] => Select * from test_one )
Getting the row count
//Getting row count of the statement
$count = $stm->rowCount();
echo("Count: " . $count . "
");
Shows Count: 5
Iterating through the rows of the PDO statement.
//PDO::FETCH_ASSOC means it fetches an associative array.
while ( $row = $stm->fetch( PDO::FETCH_ASSOC ) ) {
print_r($row);
echo("
");
}
Which gives:
Array ( [test_oneID] => 1 [test_char] => aaaa )
Array ( [test_oneID] => 2 [test_char] => bbbb )
Array ( [test_oneID] => 3 [test_char] => cccc )
Array ( [test_oneID] => 4 [test_char] => dddd )
Array ( [test_oneID] => 5 [test_char] => eeee )
It is very important to close the cursor to free up resources.
$stm->closeCursor();
Fetching statements into objects
$stm = $dbh->prepare("Select * from test_one");
//Simple execution of the statement
$stm->execute();
//PDO::FETCH_ASSOC means it fetches an Object.
$stm->execute();
while ( $row = $stm->fetch( PDO::FETCH_OBJ ) ) {
print_r($row);
echo("
");
}
$stm->closeCursor();
By using PDO::FETCH_OBJ we are retrieving the data to a stdClass Object, which is PHP’s base class for Anonymous Objects
what we get in return then is:
stdClass Object ( [test_oneID] => 1 [test_char] => aaaa )
stdClass Object ( [test_oneID] => 2 [test_char] => bbbb )
stdClass Object ( [test_oneID] => 3 [test_char] => cccc )
stdClass Object ( [test_oneID] => 4 [test_char] => dddd )
stdClass Object ( [test_oneID] => 5 [test_char] => eeee )
Extracting to a predefined object
First we define a Mock object, and then we set the fetch mode to this object.
//A Mock Object used for testing
class Test {
private $test_oneID;
public $test_char;
public $another_att ="predefined value";
static $static_att = "An Static Attribute";
}
$stm = $dbh->prepare("Select * from test_one");
$stm->execute();
//Determining the Class to be fetched to.
$stm->setFetchMode( PDO::FETCH_CLASS, 'Test');
$row = $stm->fetch( PDO::FETCH_CLASS );
print_r($row);
$stm->closeCursor();
Which gives us the data of the first row inserted into an instance of our mock object:
Test Object
(
[test_oneID:private] => 1
[test_char] => aaaa
[another_att] => predefined value
)
It is important to remark that there are 2 ways to do this
we could have done the same thing by changing:
$row = $stm->fetch( PDO::FETCH_CLASS );
to:
$row = $stm->fetchObject('Test');
Fetching a set of rows to a predefined object
We could have also fetched the full set of results to our mock class.
$stm = $dbh->prepare("Select * from test_one");
//For fetching a set of rows to objects.
$stm->execute();
while( $row = $stm->fetchObject('Test')) {
print_r($row);
echo("
");
}
Which will give us:
Test Object ( [test_oneID:private] => 1 [test_char] => aaaa [another_att] => predefined value )
Test Object ( [test_oneID:private] => 2 [test_char] => bbbb [another_att] => predefined value )
Test Object ( [test_oneID:private] => 3 [test_char] => cccc [another_att] => predefined value )
Test Object ( [test_oneID:private] => 4 [test_char] => dddd [another_att] => predefined value )
Test Object ( [test_oneID:private] => 5 [test_char] => eeee [another_att] => predefined value )
Binded PDO Statements
One of the big advantaged of PDO is the use of Binded Statements, because:
- They prevent SQL Injections
- They generally mean a huge speed increase, because of the preparation of the statement in the DB before execution.
A simple example
$stm2 = $dbh->prepare("Select * from test_one where test_oneID = :test_oneID");
$stm2->bindValue(":test_oneID", 3);
$stm2->execute();
echo("
");
$row = $stm2->fetchObject('Test');
print_r($row);
Which returns:
Test Object ( [test_oneID:private] => 3 [test_char] => cccc [another_att] => predefined value )
Dealing with statement errors
Now let’s suppose the following code, where we are trying to fecth data from a non existent table:
$sth = $dbh->prepare("Select * from non_existing table");
$sth->execute();
$row = $sth->fetch( PDO::FETCH_ASSOC );
print_r($row);
Well, this code will produce no result at all. To access the PDO Statement error, we must explicitly address it.
$arr = $sth->errorInfo();
print_r($arr);
Which will show:
Array ( [0] => 00000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘table’ at line 1 )
Setting PDO to treat PDO Statements errors as exceptions
With the following line, we can set up the PDO object to treat all PDO Statements as Exceptions
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Now we can deal with statement errors as exceptions, for example:
$dbh = new PDO('mysql:host=localhost;dbname=test', 'test_user', 'test_user_password');
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $dbh->prepare("Select * from test_onedfgsd");
try {
$sth->execute();
} catch ( Exception $e) {
var_dump($e);
}
Which will give us:
object(PDOException)#3 (7) {
["message:protected"]=>
string(92) “SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘test.test_onedfgsd’ doesn’t exist”
["string:private"]=>
string(0) “”
["code:protected"]=>
string(5) “42S02″
["file:protected"]=>
string(36) “/home/www/ttiw/article.PDO_tests.php”
["line:protected"]=>
int(231)
["trace:private"]=>
array(1) {
[0]=>
array(6) {
["file"]=>
string(36) “/home/www/ttiw/article.PDO_tests.php”
["line"]=>
int(231)
["function"]=>
string(7) “execute”
["class"]=>
string(12) “PDOStatement”
["type"]=>
string(2) “->”
["args"]=>
array(0) {
}
}
}
["errorInfo"]=>
array(3) {
[0]=>
string(5) “42S02″
[1]=>
int(1146)
[2]=>
string(40) “Table ‘test.test_onedfgsd’ doesn’t exist”
}
}Well that pretty much wraps up the basics of PDO. As a side note I would suggest that instead of using try-catch on every PDO Connection or PDO Statement, to better use a higher level exception handler and write some code for PDO Exceptions. For how to achieve this see set_exception_handler PHP Function.
