Performing CRUD Operations in MySQL - MySQL

So far, so good. Having already showed the source code corresponding to the caching and data access layers of this sample application, the only thing left to do is to show the definition of the pertaining user model:

(UserModel.php)

<?php

class UserModel
{
    protected $_adapter;
    protected $_cache;
   
    /**
     * Constructor
     */
    public function __construct(DatabaseAdapterInterface $adapter, CacheableInterface $cache)
    {
        $this->_adapter = $adapter;
        $this->_cache = $cache;
    }
   
    /**
     * Fetch a user by their ID
     */
    public function fetchById($id)
    {
        // try to fetch user data from the cache system
        if ($user = $this->_cache->get($id)) {
            return $user;
        }
        // otherwise fecth user data from the database
        $this->_adapter->select('users', "id = $id");
        if ($user = $this->_adapter->fetch()) {
            $this->_cache->set($id, $user);
            return $user;
        }
        return null;
    }
   
    /**
     * Fetch all users
     */
    public function fetchAll()
    {
        // try to fetch users data from the cache system
        if ($users = $this->_cache->get('all')) {
            return $users;
        }
        // otherwise fecth users data from the database
        $this->_adapter->select('users');
        $users = array();
        while ($user = $this->_adapter->fetch()) {
            $users[] = $user;
        }
        if (count($users) !== 0) {
           $this->_cache->set('all', $users);
        }
        return $users;
    }
   
    /**
     * Insert new user data (the cache is cleared also)
     */
    public function insert(array $data)
    {
        if ($insertId = $this->_adapter->insert('users', $data)) {
            $this->_cache->clear();
            return $insertId;
        }
        return false;
    }
   
    /**
     * Delete the table row corresponding to the specified user (the cache is cleared also)
     */
    public function delete($id)
    {
        if ($affectedRow = $this->_adapter->delete('users', "id = $id")) {
            $this->_cache->clear();
            return $affectedRow;
        }
        return false;
    }   
}


Although it’s fair to admit that the previous model is way too coupled to the underlying storage (which isn’t something that I find particularly appealing), its structure is pretty flexible, since the collaborators that it accepts in its constructor can be easily swapped at runtime.

In the next section I’m going to create a script which will use this sample model to fetch, insert and delete users from a sample MySQL table.

Using the model with the APC cache-backend: performing CRUD operations in a sample database

The first example will show how to use the user model to run CRUD operations in a MySQL table. But before you see how the example in question looks, here’s the table that will be queried (indirectly) by the model:



Even though the “users” table only contains a couple of rows, it comes in handy for illustrating the actual functionality of the sample model. What’s more, the following script shows how to take advantage of the model for fetching existing users, then inserting a new one and finally removing a row from the table:

<?php

// example using the ApcCache class

// include the autoloader
require_once 'Autoloader.php';
Autoloader::getInstance();

// create a user model
$userModel = new UserModel(new MySQLAdapter(array('host', 'user', 'password', 'database')), new ApcCache);

// fetch a single user and display their data
$user = $userModel->fetchById(1);


echo ' First Name: ' . $user->fname .
     ' Last Name: ' . $user->lname .
     ' Email: ' . $user->email;

// fetch all the users and display their data
$users = $userModel->fetchAll();
foreach ($users as $user) {
echo ' First Name: ' . $user->fname .
     ' Last Name: ' . $user->lname .
     ' Email: ' . $user->email . '<br />';
}

// insert a new user
$userModel->insert(array(
    'fname' => 'Susan',
    'lname' => 'Norton',
    'email' => 'susan@domain.com'
));

// delete an existing user
$userModel->delete(1);



To keep the whole code sample clean and uncluttered, I haven’t included the definition of the autoloader. This shouldn’t be an obstacle, however, for grasping how the above example works. Once the model has been properly instantiated, it’s utilized to perform the aforementioned CRUD operations in the sample MySQL table.

Aside from this, you should pay close attention to the way the model has been spawned. Effectively, an instance of the APC cache class has passed in to its constructor, which implies that each time a finder is called (be it “findById()” or “findAll()”), the fetched data will be cached in shared memory.

At this time you’ll be wondering if it’s feasible to instruct the model to cache user data using the file system, instead of RAM. Well, the answer is … yes! In the coming section I’m going to redefine the earlier script, which this time will pass to the model’s constructor an instance of the “FileCache” class.

Setting up a final example: using the model with the “FileCache” class

Since the model’s constructor only type hints segregated interfaces, instead of concrete implementations, it’s really simple to use it with different cache back-ends or even with distinct database adapters. To see how this can be accomplished in a snap, take a look at the following script, which looks very similar to the one created before, only that in this case the model caches user data using the file system:

<?php

// example using the FileCache class

// include the autoloader
require_once 'Autoloader.php';
Autoloader::getInstance();

// create a user model
$userModel = new UserModel(new MySQLAdapter(array('host', 'user', 'password', 'database')), new FileCache);

// fetch a single user and display their data
$user = $userModel->fetchById(1);
echo ' First Name: ' . $user->fname .
     ' Last Name: ' . $user->lname .
     ' Email: ' . $user->email;

// fetch all the users and display their data
$users = $userModel->fetchAll();
foreach ($users as $user) {
echo ' First Name: ' . $user->fname .
     ' Last Name: ' . $user->lname .
     ' Email: ' . $user->email . '<br />';
}

// insert a new user
$userModel->insert(array(
    'fname' => 'Susan',
    'lname' => 'Norton',
    'email' => 'susan@domain.com'
));

// delete an existing user
$userModel->delete(1);



There you have it. See how simple is to create a model layer that swaps between several different cache back-ends when fetching data from a given storage? And best of all, most of this flexibility has been achieved thanks to the combined use of segregated interfaces and dependency injection.

Of course, it’s dead simple to make the model accept other caching systems, (i.e. “XCache” and “Memcached”). The beauty of this is that the whole extension process would be reduced to defining a new implementer of the previous “CacheableInterface” interface and nothing else. The model’s source code wouldn’t be changed at all.

So, what are you waiting for? Build your own wrapper for some of the mentioned cache libraries and inject it straight into the model’s internals. It’ll be a pretty instructive experience, as you’ll learn one or two more things on how to take advantage of interface segregation in PHP.

Final thoughts

Unfortunately, we’ve come to the end of this series. But, overall this humble journey has been didactical, as you learned (or at least that was my intention from the very beginning) how to use a few segregated interfaces in the deployment of several PHP projects, ranging from the construction of dynamic registry and a caching system, to the development of a simple model layer that interacts with a MySQL database.

While you should evaluate these examples only by what they are, they come in handy for demonstrating how to create classes whose functionality is achieved thanks to the implementation of fine, highly-granular contracts. What’s more, if you’re still hesitating about using interfaces when building your own object-oriented applications, think twice: even in a weakly-typed language like PHP, design by contract has a lot of sense, even at expenses of writing a few more lines of code. In the end, the payback will be invaluable, trust me.

See you in the next PHP development tutorial!     

 

>>> More MySQL Articles          >>> More By Alejandro Gervasio

blog comments powered by Disqus