Get entry revisions in ExpressionEngine

Posted on May 10, 2013

ExpressionEngine is having a good feature of channel entry versioning in which entry’s versions can be saved. This feature can be enabled from the channel preferences under “Versioning Preferences”.

Versioning Preferences

Once this preference has been set, while saving an entry of that channel, previous versions or previous data of entry will be saved. These revisions of can be seen under the “Revisions” tab of “Edit Entry” page of Control Panel.

Entry Edit Page of Control Panel

You can see that the previous revisions can be loaded by clicking at “Load Revision”.

In website development, it may require to populate the previous changes made within an entry. To accomplish such type of requirement one can use entry revision feature. But I couldn’t get any guidance so that we can fetch and populate the entry revisions at front end so that a visitor can see when and what was updated for an entry.

So let’s achieve it.

The entry version data is being saved within the database table “exp_entry_versioning” but in the serialized format. I tried with SQL usingĀ Query Module to get entry versions data using MySQL function “SUBSTRING_INDEX” like:

{exp:query sql="SELECT version_date,
SUBSTRING_INDEX(SUBSTRING_INDEX(version_data,';', 16),':',-1) AS entry_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(version_data,';', 22),':',-1) AS title,
SUBSTRING_INDEX(SUBSTRING_INDEX(version_data,';', 30),':',-1) AS url_title
FROM exp_entry_versioning
WHERE channel_id=1 AND author_id=1
ORDER BY version_date DESC"}
<p>{entry_id} :: {title} :: {url_title} :: {version_date format="%d-%m-%Y"}</p>

But above will not work properly šŸ™ and its really not a good approach. I think, MySQL couldn’t deal with serialized data.

We can do it properly via PHP with unserialize function:

$version_data = $this->EE->db->select('version_data, version_date')
 ->get_where('entry_versioning', array('entry_id' => $entry_id))

foreach($version_data as $entry_data)
 $version_date = $this->EE->localize->format('%Y-%m-%d %H:%i', $entry_data['version_date']);
 $edata = unserialize($entry_data['version_data']);

 echo 'Version Date: '.$version_date.'<br />';
 echo 'Title: '.$edata['title'].'<br />';
 echo 'URL Title: '.$edata['url_title'].'<br />';

In above PHP code example, one can fetch/print other data of channel entry with array $edata.

Please Note: I developed a pluginĀ Entry Revisions. It would be better to use plugin.