PHP Database Objects PDOs for short is a light-weight data abstraction layer for PHP. What this means is that it allows you to use an interface for performing data manipulations instead of using db specify functions such as mysql_query. So that if you needed to migrate to a different database you would not have to rewrite your code instead you simply change the connection driver. In addition to portability of the code you also protect against SQL-Injection as the prepare method will call the underlying quote method of the specific driver so that your input gets escaped properly.
Connecting to a database with PDO
To connect to a database you need to instantiate the PDO object. The constructor espects 3 parameters: connection string, username, and password. The connection string specifies the driver, host, and optional database or schema to connect to and has the following format: driver:host=hostname:port;dbname=database
$pdo = new PDO("mysql:host=localhost;dbname=mysql", "username", "password");When establishing a connection if there is an error connecting an exception will be thrown. In this thrown exception the stack trace will reveal the username and password used to access the server. It is very important that you capture these exceptions and either throw an excpetion that does not share this information or define your own handlers. Below is a full example for connection to a databse with PDO and handling the Exception if the connection fails.
$driver = "mysql";
$host_name = 'localhost';
$user_name = 'root';
$password = 'root';
$db_name = 'MY_DB';
try {
$db = new PDO("$driver:host=$host_name;dbname=$db_name", $user_name, $password);
echo 'Connected to database';
}
catch(PDOException $e) {
echo $e->getMessage();
}Preparing and Executing a Query
There are 2 main ways to prepare queries when using PDOs. The first method is to define your sql with ? placeholders for variables.
$sql = 'SELECT name, phone_num, active
FROM Users
WHERE name like ?';
$statement = $pdo>prepare( $sql );The second option is define the query with named variables by use of the : character. In the below query we defined the named variable :name.
$sql = 'SELECT name, phone_num, active
FROM Users
WHERE name like :name';
$statement = $pdo>prepare( $sql );The PDOStatement object is returned from the prepare function. The next step will be to execute the statements with your variables. If you used the ? method you simply pass an array of variables.
$statement->execute(array('A%'));While if you used the named parameter way you will pass in an assocaited array of the variables
$statement->execute( array( ":name" => "A%" ) );
Now that you have executed the query me can fetch the results in various different methods.
Fetching and Using the Result
Now that you have executed your query you want your results right? To do this you utilize either the fetch or fetchAll method. Which will return the next result row or an array of all the rows. You can even specify how you want the data returned as: FETCH_ASSOC returns an associated array by the column name and FETCH_BOTH returns it by both the index of the column and the name.
$result = $sth->fetch(PDO::FETCH_ASSOC);
$result = $sth->fetchAll(PDO::FETCH_BOTH);
Now that you have the data simply use it however you need in your application. If you returned it as an associated array you can simply get the property.
Closing your Connection
To close your connection you simply need to set all your references to the PDO object to null. If you fail to close the connection it will be closed automatically upon the completion of the script. Good practice is to close it once you are finished.
Resources