Joomla Developers Challenge

Who’s up for a little challenge? Everyone is invited to join in, even if you’re a beginning php developer. We’re going to tackle a simple problem. The winner gets eternal Joomla stardom!

The Problem

Imagine a simple e-commerce system. The shop owner can manage products, and show them on the front page. The owner wants to able to give some products more prominence, by ordering the list. After a while, because of all the products being added, removed and reordered, the database table becomes messy.

The Challenge

This is the table:

CREATE TABLE `jos_products` (
`id` SERIAL,
`name` VARCHAR( 100 ) NOT NULL ,
`ordering` INT NOT NULL
);

The table might look a little like this:

+----+------------+----------+
| id | name       | ordering |
+----+------------+----------+
|  6 | Chocolate  |       25 |
|  8 | Beer       |        2 |
| 19 | Cookies    |       19 |
| 21 | Lemonade   |        8 |
+----+------------+----------+

We want the ‘ordering’ column to be sequential, like this:

+----+------------+----------+
| id | name       | ordering |
+----+------------+----------+
|  6 | Chocolate  |        4 |
|  8 | Beer       |        1 |
| 19 | Cookies    |        3 |
| 21 | Lemonade   |        2 |
+----+------------+----------+

Your job is to write a bit of code to clean up the mess.

The rules

To win, your code should be short, elegant, and performant, with as little queries as possible. Obviously, your code should use the Joomla framework. PHP 5.2 and MySQL 5 are assumed. All we need is the actual code to fix the table, not the complete component. So your submission should look like this:

function cleanup_ordering() {
$db = JFactory::getDBO();
/* your code here */
}

Please don’t go hunting the net for ready made solutions, that would kill the fun. The code should be your own work. Don’t post your submissions here, but mail them to mathias {AT} joomlatools {DOT} eu.

I will post my own solution when the contest is over. This will serve as the proof:

echo md5(file_get_contents('solution.php'));
// Hash of my solution: d5dc5903a8d57ea28af5c618ec92418e