Confessions of an apprentice: How to manipulate data from an eZ Publish database with PHP

And that is a good thing, because until now, you have been limited to creating sites using the default eZ Publish functionalities. Which is enough for certain types of sites, and certain types of clients. But, as it is true in every line of work, clients tend to always ask for more. And a good programmer is ready for each and every type of situation.

This will be an extensive article covering the creation of custom database tables and explaining how to work with them in PHP. Therefore, the terms “object” and “class” we’ll be using in the rest of this article don’t refer to the eZ Publish objects and classes, but to the PHP ones.

Creating custom database tables and manipulating them

What is the theory behind all of this?

eZ Publish uses classes which extend the eZPersistentObject class to manipulate data in the database tables. One class per database table. The instances of this class (and all of those which extend the eZPersistentObject class) represent a row which is stored inside a table.

Now, the eZPersistentObject class represents a handy tool (or a set of tools, if you will) which enables us to manipulate the data in a table without the need od writing SQL queries all the time. Which is really great, because we are protected from SQL injection that way, also.

So, I will show you what the proper steps are in creating and manipulating a new database table for your project in the rest of this article.

Creation of a database table

Let’s say, for example, that you have set up a small web-shop and now you need to create some promotional discount codes for the users in order to make your products even more approachable. Those discount codes don’t always work, however, because you intend to apply them on certain dates or on certain periods of time.

The first thing we need to do is to create a table in our database which will store our discount codes. This table will need at least four fields: the discount code itself, the date when discount code validity starts, the date when discount code validity ends, and the percentage of the discount applied. We’ll also need an ID which will serve as the primary key on this table.

Create a new table like so:

CREATE TABLE discount_code (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    discount_name VARCHAR(255) NOT NULL,
    valid_from INT(11) NOT NULL,
    valid_to INT(11) NOT NULL,
    discount_amount FLOAT NOT NULL
);

And there you have it, the structure of your table is ready. So, what did we do with this MySQL statement? Well, first off, we told SQL that we want to define a new table in our site’s database called discount_code. In it we’ve defined the structure we outlined in this article. So, we have a primary key called id, the name of our discount code called discount_name, values for the dates of the start and end of validity for our code, and the percentage we wish for our code to have.

Manipulating the data in our custom database table

After we’ve outlined our database table in this manner, we need to create its PHP class so that we can manipulate the data in the table itself. But before we actually create this class, let’s take a look at what we need to do with the rows in our table. We need to be able to create them, store any changes we might apply to them, and we need to be able to fetch those codes in a certain manner.

In regards to creation, storage and removal of rows of data, the eZPersistentObject class already has some methods which can help you in those tasks. As for the fetching the data, we need to determine what kind of fetches we need to implement. So, let’s say that we need to be able to fetch a table row by its ID, by its name, and a list of all the rows in a table. After that, we also need to know how many rows have already been created, and let’s say that we need to know if a row is actually applicable so that it can be used.

We will create a class which extends the eZPersistentObject class, and its instances (objects) will manipulate the data in our table. We will put it in the classes folder of the module we have created before.
 If you don’t have a classes folder for your module (for all intents and purposes, I’ll assume that it’s the mymodule we created earlier), please create it now. So, go to extension/myextension/classes folder and in it create a PHP file called mydiscountcode.php.

Open mydiscountcode.php and insert the following lines of code in it:

<?php
class myDiscountCode extends eZPersistentObject
{

function __construct($row)
{
    parent::__construct($row);
}

static function definition()
{
    return array(
                'fields' => array(
                            'id'=>array('name'=>'ID', 'datatype'=>'integer', 'default'=>0, 'required'=>true),
                            'discount_name '=>array('name'=>'DiscountName', 'datatype'=>'string', 'default' => '', 'required' => true),
                            'valid_from'=>array('name'=>'ValidFrom', 'datatype'=>'integer', 'default'=>0, 'required'=>true),
                            'valid_to'=>array('name'=>'ValidTo', 'datatype' => 'integer', 'default' &nbsp;=> 0, 'required' => true),
                            'discount_amount'=>array('name'=>'DiscountAmount', 'datatype' => 'float', 'default' => 0, 'required' => true)
                ),
                'keys' => array('id'),
                'class_name' => 'myDiscountCode',
                'sort' => array('valid_from' => 'desc'),
                'name' => 'discount_code'
    );
}

function isValid()
{
    $currentTimeStamp = time();
    if ( $currentTimeStamp >= (int) $this->attribute('valid_from') && $currentTimeStamp < (int) $this->attribute('valid_to') )
        return true;
    return false;
}

static function fetch($id)
{
    return eZPersistentObject::fetchObject(self::definition(), null, array('id' => (int) $id));
}

static function fetchByDiscountName($discountName)
{
    return eZPersistentObject::fetchObject(self::definition(), null, array( 'discount_name' => trim($discountCode) ) );
}

static function fetchCount()
{
    return eZPersistentObject::count(self::definition());
}

static function fetchList($conds = null, $limit = null)
{
    return eZPersistentObject::fetchObjectList(self::definition(), null, $conds, null, $limit);
}
}
?>

As you can see, we have defined a class called myDiscountCode which extends the already existing eZPersistentObject class. The constructor used here is very simple and just calls the constructor of the parent class to do the initialization for us. Also, most of the code here is reused from the eZPersistentObject class because it already has most of the functionalities we need.

Each and every class extending eZPersistentObject class has a definition() method which returns a single associative array. But it’s an important one because with this array, eZ Publish knows which table from the database to use, and what attributes/properties of a class to link with what values in the table we’ve defined before. Let’s break this array down to its fundamental elements.

It consists of five keys: fields, function_attributes, keys, class_name, sort and name. The fields key is the link from the class attributes to their respective database columns, and it’s also an associative array of values. As you can see, each attribute needs to have some information on which it is based. So, we need to define its name, its datatype, a default value, and whether it’s required or not. All of these, except the name, need to match the properties of the corresponding database column. On the other hand, for the name value you can assign any value you want.

The keys key is an array of fields used to uniquely define each row in our table. Since our table has a primary key called id, and it’s unique, this is the value we want the keys array to have.

The class_name key is the name of our class which extends the eZPersistentObject class.

The sort key is the default sorting mechanism you use when fetching table rows. In here we have said that we wish to sort our discount codes by using the valid_from attribute, descending, which means that the newest date in the database will be first, and the oldest one last.

And, finally, the name key contains the name of the actual database table we store our data in.

After we have created the definition array, we need to fill our class with some methods to work with our data.

So, the first method is the isValid() method, which checks the validity of the discount code in question. As you can see, it checks the current time, and if the current time is between the valid_from and valid_to times, it returns a value of true. Otherwise, it will return a false value.

After that, we have defined a static fetch() method which fetches the discount codes by their ids and returns the array of instances of our class. Each instance wraps one row from the database table. In here we actually use the inherited eZPersistentObject method called fetchObject() which accepts the following values: the definition array (which we created earlier), some field filters (which is currently set to null because we have no filters to apply - these filters are used only if you want to fetch some of the records from the database table), and the conditions by which we want to fetch our discount code (which in this case is defined by the array that specifies the ID of the row to fetch).

Since our table is fairly simple, all other methods also use methods from the eZPersistentObject class. You can find a full reference to the eZPersistentObject class here.

Since this is such a major topic, I’m going to end today’s article on this note. We will explore how to use instances of our class in eZ Publish templates.

Until next time, I wish you happy coding,
Tomislav