PHP Data Objects (PDO)


PHP Data Objects (PDO)


The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.
PDO is a PHP extension to formalize PHP’s database connections by creating a uniform interface. This allows developers to create code which is portable across many databases and platforms. PDO is _not_ just another abstraction layer like PearDB although PearDB may use PDO as a backend. Those of you familiar with Perls DBI may find the syntax disturbingly familiar.

Connect to a database

Every interaction with a database begins with a connection. Regardless of the database you use, you must connect first and establish a database handler. After connecting you your database of choice, much of the PDO methods are similar. This is why PDO is such a powerful and useful tool for PHP. Here we show how to connect to various databases and establish a database handler object that we can use for further interaction with the database.

Connect to MySQL


MySQL is the choice of many web developers and will be used as the database of choice for much of this tutorial. Here we see how to connect to a MySQL database.

?php

/*** mysql hostname ***/


$hostname = ‘localhost’ ;


/*** mysql username ***/


username = ‘username’ ;


/*** mysql username ***/


password = ‘password’ ;


try {



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




/*** echo a message saying we have connected ***/

echo ‘Connected to database’;

}

catch(
PDOException $e)

{

echo
$e->getMessage }

?>


Close a Database Connection


Up to this point we have seen how to connect to a database using PDO. But of course, we also need to disconnect when we have finished. To close the connection the object needs to be destroyed so that no reference to it remains. This is normally done at the end of a script where PHP will automatically close the connection. However, the connection may be close implicitly by assigning the value of null to the object as seen below.

?php

/*** mysql hostname ***/


$hostname = ‘localhost’ ;


/*** mysql username ***/


username = ‘username’ ;


/*** mysql username ***/


password = ‘password’ ;


try {



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




/*** echo a message saying we have connected ***/

echo ‘Connected to database’;

}


/*** close the database connection ***/
$dbh = null;

}

catch(PDOException $e)

{

echo
$e->getMessage();

}


?>



If the database connection fails, the code to assign a null value is never called as the exception throws control to the catch block.

PDO Query


Now that we can open and close a connection to the database with PDO, we can make use of it for what databases are made for, storing and retrieving information. The simplest form of query is the PDO query method. As the name suggests, this is used to perform database queries. Before we begin to query a database, lets create a small database with a table for animals. This will be a MySQL database for use throughout much of this tutorial. Remember, because PDO provides a common set of tools for databases, once we have the correct connection, the rest of the code is the same, regardless of the database you choose. When using PDO to query a database, the function used to do so depend on the statement you wish to send to the database. Below we will see three queries on how to INSERT, SELECT and UPDATE.

INSERT


To gather information from a database, we first need to put some info into it. We use the same code from above to connect and disconnect from the database and the INSERT query is accomplished using the PDO::exec method.

?php

/*** mysql hostname ***/


$hostname = ‘localhost’ ;


/*** mysql username ***/


username = ‘username’ ;


/*** mysql username ***/


password = ‘password’ ;


try {





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




/*** echo a message saying we have connected ***/

echo ‘Connected to database’;

}


/*** INSERT data ***/

$count= $dbh-
>
exec(“INSERT INTO animals(animal_type, animal_name) VALUES (‘kiwi’, ‘troy’)”);

/*** echo the number of affected rows ***/
echo $count;

/*** close the database connection ***/
$dbh = null;

}
catch(
PDOException $e)

{
echo
$e->getMessage();

}

?>



The output of the script above will look like this:


Connected to database
1


This shows us that we connected successfully to the database and then we have displayed the number of affected rows. PDO::exec returns the number of affected rows if successful, or zero (0) if no rows are affected. This may cause issues if you are checking for a Boolean value and why it is recommended using === when to check for type also, as zero (0) may evaluate to Boolean FALSE.
The PDO::exec method should be used for SQL statements that do not return a result set. We could use this same method to INSERT many more animals to our database, but a more efficient method would be to use a transaction. This is covered in the section on Transactions.

SELECT


Unlike PDO::exec the PDO::query method returns a result set, that is, a group of information from the database in the form of a PDOStatement object. Our database should look a little like the example in the What is PDO section. Using this we can SELECT information.


$hostname = ‘localhost’ ;


/*** mysql username ***/


username = ‘username’ ;


/*** mysql username ***/


password = ‘password’ ;


try {



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




/*** echo a message saying we have connected ***/

echo ‘Connected to database’;

}


/*** The SQL SELECT statement ***/
$sql = “SELECT * FROM animals”;

foreach ($dbh->query($sql) as $row)
{
print
$row[‘animal_type’] .‘ – ‘. $row[‘animal_name’] .
;

}

/*** close the database connection ***/
$dbh = null;
}
catch(
PDOException $e)

{

echo $e->getMessage();
}

?>


From the script above, we can expect the results to look like this:


Connected to database
emu – bruce
funnel web – bruce
lizard – bruce
dingo – bruce
kangaroo – bruce

wallaby – bruce
wombat – bruce
koala – bruce
kiwi – troy


You will have noticed that we can iterate over the result set directly with foreach. This is because internally the PDO statement implements the SPL traversble iterator, thus giving all the benifits of using SPL. For more on SPL refer to the Introduction to SPL page. The greatest benefit of this is that SPL iterators know only one element at a time and thus large result sets become manageable without hogging memory.

UPDATE

To update a field in a database with PDO we once again use the PDO::exec method in the same manner as we did with the INSERT


$hostname = ‘localhost’ ;

/*** mysql username ***/


username = ‘username’ ;


/*** mysql username ***/

password = ‘password’ ;


try {




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




/*** echo a message saying we have connected ***/

echo ‘Connected to database’;

}


/*** INSERT data ***/

$count = $dbh

>exec(“UPDATE animals SET animal_name=’bruce’ WHERE animal_name=’troy’”);

/*** echo the number of affected rows ***/
echo $count;

/*** close the database connection ***/
$dbh = null;
}
catch(
PDOException $e)

{

echo $e->getMessage();
}

?>

Leave a Reply