MDBitz Development Resources

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.

echo $result->name;

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.

$pdo = null;

Resources

• PHP.net – PHP Data Objects
FETCH, fetchAll, MySQL, PDO, PDOStatement, PHP, SQL, SQL Injection

Those familiar with SQL will have most likely used the UNION command when they want to combine select statements into 1 result set. Most often individuals will use UNION without understanding that additional work is done behind the scenes to remove duplicate entries. That is why it is important to understand your database and to know if there is the possiblity of duplicate entries. If there is no chance for duplicate entries or if you want duplicates in your results then you would want to use the UNION ALL command that simply joins the 2 result sets without using any worktables enhancing its performance.

To illustrate lets assume we have an activity log table that stores users activities within an application. In addition we have a log history table that actions get moved to at the end of the day so that the inserting of daily records does not get bogged down by the table having thousands/millions of rows.

Now lets assume we have a monitoring application that allows priveledged individuals to do searches on a user for a time period to get a list of activities they did within the application. To do this most individuals would do a UNION on the LOG and LOG_HISTORY tables.

SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS
 
UNION
 
SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG_HIST
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS

However in this case it is not possible for there to be duplicate records returned from the two tables. So instead to enhance the performance of the query we could use the UNION ALL statement.

SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS
 
UNION ALL
 
SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG_HIST
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS

Most individuals having database experience with MySQL will find shortly after using Sybase DBMS that the backslash character does not escape characters. Instead in Sybase T-SQL the single quote itself acts as the escape character. Below is a sample UPDATE statement showing the difference in escaping the single quote character in MySQL and Sybase.

MySQL

UPDATE COMPANY SET NAME = 'Joseph\'s Shoes' WHERE ID = 3

Sybase

UPDATE COMPANY SET NAME = 'Joseph''s Shoes' WHERE ID = 3