Joomla, MySQL, add-ons and Migration

I’ve just recently revived my interest in CMS (Content Management Systems) – specifically Joomla!

Why? Because I created this “portal” ( and though I seriously doubt I have the time to actively maintain it, I’ve spent way too many hours working on it, to just abandon it on the wayside.

Medha Journal has evolved since it’s inception in 2007 from a Joomla 1.x version to Joomla 1.5.x, with a large hiatus in the middle (from 2010 through the last week of december 2013), until I just upgraded it (last week) from it’s rickety 1.5 version to the latest stable 2.5x version of Joomla.

Those who don’t know what joomla is, here’s a summary –

It is a PHP based open source Content Management System that can be extended to do more or less anything, using a massive collective Extensions library (sort of like CPAN), that is community driven.

So, to get back to the topic at hand. Since I had not dabbled with Joomla (besides the user-land/power-user oriented work – primarily content editorial stuff) in a long time, I decided to take the plunge with some down time in hand (the holidays) and upgrade it from 1.5 to 2.5.

In course of this migration, I also switched from the default Joomla content management to using a tool called K2 (which realistically is more suited from social media oriented content portals, such as Medha Journal).

One major issue I ran into was this –

The Old version of the website used an extension called “jomcomment” which was developed by some folks down in Malaysia or Indonesia, and allowed from some better spam control. I used this in conjunction with the myblog extension, developed by the same group of developers, to give my users a seamless (or relatively seamless) experience as they added content and commented on each other’s works.

However, with the newer versions of Joomla that currently are active (2.5x and 3.x), these extensions don’t work. Over the years, we had accumulated thousands of comments on the north of 2000 articles collected on the Journal. So, it was imperative to migrate these.

K2 ( has a very nice commenting system with builtin Akismet spam filters, etc. So, the new site would obviously use this. Migration was an interesting proposition since the table structure (jomcomment to K2 comments) were not identical.

Old table looked like this:


Field Type Null Key Default Extra
id int(10) NO PRI NULL auto_increment
parentid int(10) NO 0
status int(10) NO 0
contentid int(10) NO MUL 0
ip varchar(15) NO
name varchar(200) YES NULL
title varchar(200) NO
comment text NO NULL
preview text NO NULL
date datetime NO 0000-00-00 00:00:00
published tinyint(1) NO MUL 0
ordering int(11) NO 0
email varchar(100) NO
website varchar(100) NO
updateme smallint(5) unsigned NO 0
custom1 varchar(200) NO
custom2 varchar(200) NO
custom3 varchar(200) NO
custom4 varchar(200) NO
custom5 varchar(200) NO
star tinyint(3) unsigned NO 0
user_id int(10) unsigned NO 0
option varchar(50) NO MUL com_content
voted smallint(6) NO 0
referer text NO NULL

The new table looks like this:

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
itemID int(11) NO MUL NULL
userID int(11) NO MUL NULL
userName varchar(255) NO NULL
commentDate datetime NO NULL
commentText text NO NULL
commentEmail varchar(255) NO NULL
commentURL varchar(255) NO NULL
published int(11) NO MUL 0

This called for some manipulation of the data extracted from the old jomcomment tables, before they got inserted back into the K2 comments table in the new database (both were MySQL).

So, I imported the data out of the old table using phpMyAdmin, massaged the data, modified it to fit the new table structure and imported it back in.

The hardest part was ensuring that the date field of the extracted CSV imported into the new table correctly. For that, I had to manually adjust the date format. One way is as shown here.

Another way is (if your data fits in Excel), manually set the column format (corresponding to your date field) to something that matches the MySQL format (in this case, yyyy-mm-dd hh:mm).


Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.