Solution for the Developers Challenge

(Last week I posted a little challenge for PHP developers. You might want to give it a try for yourself before reading the solution below.)

I received about 15 e-mails. Most of them were a variation of the same theme: get the id’s from the database, and update each row — which usually meant having the update query inside a foreach loop. To limit the number of queries,some people worked out a way to only update the rows that actually needed updating. One person used a stored procedure in MySql, which was then called from the function.

The winners

These people sent in the best solution (ordered!):

1. Richard Greset
2. Thomas Kahl
3. Shayne Bartlett

They win eternal Joomla glory, and a spot in Open Source Heaven (which is just as good as regular heaven, but there’s no entrance fee and you get to make your own changes!)

The solution

My solution only requires a single query:

SET @order = 0;
UPDATE jos_products SET ordering = (@order := @order + 1) ORDER BY ordering ASC;

Setting the variable in the first line obviously doesn’t count as a query. The intent should be very clear: the rows are ordered, and each row get’s a new value. The winners had some small variations, but the way it’s written above is the one that’s most readable. To see the solution as a Joomla method, download the attached zip. Of course, if you have a better solution, let’s us know.

PHP’s little brother

The reason for this little challenge, was to get an idea of what MySQL features people actually know how to use. Joomla 1.5, nor most of the code from 3PD extensions I’ve seen, make full use of MySQL. That’s a pity, because there is plenty of opportunity there to make your code faster, and more stable. There are of course the historical reasons: Joomla 1.5 still has to run on MySQL 4. But I believe many people regard MySQL as PHP’s little brother: you drag it along because you have to, but you don’t really know it all that well.