Simple Blog – Example 5: MySQLi

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

Until now we’ve been exploring different programming styles. We programmed the Simple Blog application using structured, procedural, and object oriented programming. Next, we explored the MVC pattern by physically separating our data, application, and presentation logic. So far, each example has required pervasive changes to the application. For the next few examples, however, we’ll focus on improving specific layers of the application, rather than rewriting the application as a whole.

In this example we’ll be revisiting Simple Blog’s data access layer. PHP offers three options for interacting with a MySQL database: mysql, mysqli, and pdo. So far, we’ve only used the original mysql interface. So, let’s rewrite the Database and Post classes using mysqli. The two mysqli benefits I’m most interested in are the object-oriented interface and the support for prepared statements.

Database Class

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

class Database extends mysqli 
{
	private $hostname;
	private $username;
	private $password;
	private $database;
	
	public function __construct() 
	{
		// Initialize object with database constants
		$this->hostname = DATABASE_HOST;
		$this->username = DATABASE_USER;
		$this->password = DATABASE_PASSWORD;
		$this->database = DATABASE_NAME;	
		
	    // Open database connection
		parent::__construct(
			$this->hostname, 
			$this->username, 
			$this->password, 
			$this->database
			);
	}

}

The only advantage in extending the mysqli class is that we can now simply call:

$database = new Database();

instead of:

$database = new mysqli($host, $user, $password, $dbname);

Post Class

Disclaimer! At first, I was exited to enhance Simple Blog by upgrading to mysqli. As I mentioned above, one of the key advantages to using mysqli is the availability of parameterized queries. In practice, however, it turns out that mysqli is great for running parameterized DML, but painful for parameterized SQL.

The problem with mysqli’s parameterized SQL is that the execute method does not return a database cursor, instead the execute method returns true on success or false on failure. The boolean return is fine for DML, but problematic for SQL where our sole objective is to retrieve data. To actually retrieve the data, we need to bind variables to the statement after the execute call:

$statement->bind_result($id, $title, $content, $created);

Next, we call the fetch() method to populate the bound variables with the data retrieved from the database. But, we can’t simply return these bound variables, since the caller is expecting an object in return. So, we have to instantiate the Post class and build the object with the bound variables. Following is the source code for the Post class. See the getById() method for an example of parameterized SQL.

class Post {
	
	public $id;
	public $title;
	public $content;
	public $created;
	
	public static function getBySql($sql) {
		
		// Open database connection
		$database = new Database();
		
		// Execute database query
		$result = $database->query($sql);
		
		// Initialize object array
		$objects = array();
		
		// Fetch objects from database cursor
		while ($object = $result->fetch_object()) {
			$objects[] = $object;
		}
		
		// Close database connection
		$database->close();

		// Return objects
		return $objects;	
	}
	
	
	public static function getAll() {

		// Build database query
		$sql = 'select * from post';
		
		// Return objects
		return self::getBySql($sql);
	}

	public static function getById($id) {
	
		// Build database query
		$sql = "select * from post where id = ?";
		
		// Open database connection
		$database = new Database();
		
		// Get instance of statement
		$statement = $database->stmt_init();
		
		// Prepare query
		if ($statement->prepare($sql)) {
			
			// Bind parameters
			$statement->bind_param('i', $id);
			
			// Execute statement
			$statement->execute();
			
			// Bind variable to prepared statement
			$statement->bind_result($id, $title, $content, $created);
			
			// Populate bind variables
			$statement->fetch();
		
			// Close statement
			$statement->close();
		}
		
		// Close database connection
		$database->close();
		
		// Build new object
		$object = new self;
		$object->id = $id;
		$object->title = $title;
		$object->content = $content;
		$object->created = $created;
		return $object;
	}
	
	public function insert() {
		
		// Initialize affected rows
		$affected_rows = FALSE;
	
		// Build database query
		$sql = "insert into post (title, content) values (?, ?)";
		
		// Open database connection
		$database = new Database();
		
		// Get instance of statement
		$statement = $database->stmt_init();
		
		// Prepare query
		if ($statement->prepare($sql)) {
			
			// Bind parameters
			$statement->bind_param('ss', 
				$this->title, $this->content);
			
			// Execute statement
			$statement->execute();
			
			// Get affected rows
			$affected_rows = $database->affected_rows;
				
			// Close statement
			$statement->close();
		}
		
		// Close database connection
		$database->close();

		// Return affected rows
		return $affected_rows;			
	}

	public function update() {
	
		// Initialize affected rows
		$affected_rows = FALSE;
	
		// Build database query
		$sql = "update post set title = ?, content = ? where id = ?";
		
		// Open database connection
		$database = new Database();
		
		// Get instance of statement
		$statement = $database->stmt_init();
		
		// Prepare query
		if ($statement->prepare($sql)) {
			
			// Bind parameters
			$statement->bind_param('ssi', 
				$this->title, $this->content, $this->id);
			
			// Execute statement
			$statement->execute();
			
			// Get affected rows
			$affected_rows = $database->affected_rows;
				
			// Close statement
			$statement->close();
		}
		
		// Close database connection
		$database->close();

		// Return affected rows
		return $affected_rows;			

	}

	public function delete() {

		// Initialize affected rows
		$affected_rows = FALSE;
	
		// Build database query
		$sql = "delete from post where id = ?";
		
		// Open database connection
		$database = new Database();
		
		// Get instance of statement
		$statement = $database->stmt_init();
		
		// Prepare query
		if ($statement->prepare($sql)) {
			
			// Bind parameters
			$statement->bind_param('i', $this->id);
			
			// Execute statement
			$statement->execute();
			
			// Get affected rows
			$affected_rows = $database->affected_rows;
				
			// Close statement
			$statement->close();
		}
		
		// Close database connection
		$database->close();

		// Return affected rows
		return $affected_rows;			
	
	}
}

Upon further research, I found a few hacks on stackoverflow.com that automagically build result sets after calling mysqli’s execute method. But even so, the result sets still need to be converted into objects prior to return. Surprisingly, I found that most developers are not using parameterized SQL in conjunction with mysqli. Instead, they are using mysqli’s query() function and manually building queries using string concatenation and sprintf, which defeats the purpose of parameterized queries. For these reasons, I’m ready for the next example where we’ll rewrite Simple Blog’s data access layer using PHP Data Objects.

Next

Rewriting the data access layer using PHP Data Objects
Simple Blog – Example 6

Download

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

Advertisements

One thought on “Simple Blog – Example 5: MySQLi

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