Simple Blog – Example 6: PDO

Simple Blog is a multi-part series. Check out The Index

In the last example, Simple Blog – Example 5, we rewrote the application’s data logic using PHP’s mysqli extension. In this example, we’ll rewrite the application’s data logic again, but this time using PHP Data Objects. PDO offers significant advantages over PHP’s original mysql extension.

First, PDO offers a fully object-oriented interface through its PDO, PDOStatement, and PDOException classes. Second, PDO helps protect our application from SQL injection attacks by using parameterized statements. Third, PDO offers transactional support to commit or rollback multiple statements at once. Finally, PDO offers a consistent interface across multiple database drivers which greatly enhances the portability of our application.

Database Class

Since PDO already offers an object oriented interface, I decided not to write a wrapper class. The new Database class extends the PDO class and performs the simple task of connecting to the database engine and selecting the target database.

class Database extends PDO 
{
	public function __construct() 
	{	    
	    try 
	    {
	   		// Build PDO data source name for MySQL connection
		    $dsn = "mysql:host=".DATABASE_HOST.";dbname=".DATABASE_NAME;

		    // Open database connection
			parent::__construct($dsn, DATABASE_USER, DATABASE_PASSWORD);	
		}
		catch (PDOException $exception) 
		{
			die($exception->getMessage());
		}
	}
}

Extending and initializing the PDO class allows us to simply call:

$database = new Database();

instead of:

$database = new PDO("mysql:host=$hostname;dbname=blog", $username, $password);

My only real gripe with PDO is the necessity to build driver specific connection strings using concatenation or sprintf. For example:

$dsn = "mysql:host=".DATABASE_HOST.";dbname=".DATABASE_NAME;

Personally, I would prefer something more object-oriented like:

$database = new PDO();
$database->driver = mysql;
$database->hostname = DATABASE_HOSTNAME;
$database->username = DATABASE_USERNAME;
$database->password = DATABASE_PASSWORD;
$database->dbname = DATABASE_DBNAME;
$database->connect();

Post Class

Let’s take a look at three key methods of the Post class.

1. Method to retrieve all posts using PDO’s query method.

public static function getBySql($sql) 
{
	try
	{
		// Open database connection
		$database = new Database();

		// Set the error reporting attribute
		$database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		// Execute database query
		$statement = $database->query($sql);
		
		// Fetch results from cursor
		$statement->setFetchMode(PDO::FETCH_CLASS, __CLASS__);
		$result = $statement->fetchAll();
		
		// Close database resources
		$database = null;
		
		// Return results
		return $result;
	}
	catch (PDOException $exception) 
	{
		die($exception->getMessage());
	}		
}

public static function getAll() 
{
	$sql = 'select * from post';
	return self::getBySql($sql);				
}
  • First, we write a wrapper for PDO’s query method named getBySql(). The getBySql method is a helper that connect to the database, executes any sql statement, and returns an array of objects to the caller. Each object in the array corresponds to a row in the database.
  • Next, we write the getAll method which builds a sql statement to retrieve all rows and passes the statement as a parameter to getBySql($sql).
  • Finally, notice how the getBySql method is wrapped in a try-catch block. This allows us to take advantage of PDO’s exception handling. Also, note how we set our error reporting attribute to PDO::ERRMODE_EXCEPTION

2. Method to retrieve a single post using parameterized SQL:

public static function getById($id) 
{
	try
	{
		// Open database connection           
		$database = new Database();

		// Set the error reporting attribute
		$database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
					
		// Build database statement
		$sql = "select * from post where id = :id limit 1";
		$statement = $database->prepare($sql);
		$statement->bindParam(':id', $id, PDO::PARAM_INT);			
					
		// Execute database statement
		$statement->execute();
		
		// Fetch results from cursor
		$statement->setFetchMode(PDO::FETCH_CLASS, __CLASS__);
		$result = $statement->fetch();

		// Close database resources
		$database = null;
		
		// Return results
		return $result;
	}	
	catch (PDOException $exception) 
	{
		die($exception->getMessage());
	}
}
  • As you can see, the getByID method does not call PDO’s query method. Instead, we instantiate PDO’s PDOStatement class in order to build a parameterized SQL statement.
  • First, we build the query using named placeholders such as where id = :id.
  • Next, we bind the $id variable to the statement using bindParam and specify the parameters data type using PDO::PARAM_INT.
  • Finally, we execute the statement, fetch the result and return a single object to the caller.

3. Method to insert a new post using parameterized DML:

private function insert() 
{	
	try
	{
		// Open database connection
		$database = new Database();
		
		// Set the error reporting attribute
		$database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		// Build database statement
		$sql = "insert into post (title, content) values (:title, :content)";
		$statement = $database->prepare($sql);
		$statement->bindParam(':title', $this->title, PDO::PARAM_STR);
		$statement->bindParam(':content', $this->content, PDO::PARAM_STR);
		
		// Execute database statement
		$statement->execute();
		
		// Get affected rows
		$count = $statement->rowCount();
		
		// Close database resources
		$database = null;
		
		// Return affected rows
		return $count;
	}
	catch (PDOException $exception) 
	{
		die($exception->getMessage());
	}			
}
  • First, we build the insert statement using named placeholders such as :title and :content and prepare the statement using PDO’s prepare method.
  • Next, we bind the values of $this->title and $this->content using bindParam. Since we’re working with strings we specify the PDO::PARAM_STR data type
  • Next, we execute the statement and retrieve the number of rows affected.
  • Finally, we return the number of row affected to the caller

Notice that we’ve upgraded our entire application with secure database operations, transactional support, and enhanced portability all by modifying only our data layer (i.e Database and Post classes). Our controllers and views will consume these new methods without modification. Now, that’s the modular approach we’re looking for =)

Next

Implementing the Smarty Template Engine to render our views.
Simple Blog – Example 7

Download

You can download the source code for each example at box.net.
Download Source

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s