End Your If

Yesterday, at work, it occurred to me, not for the first time, that something that comes so easily to me, does not to others.  I had tasked someone with, what seemed a simple task, to retrieve a list of users who have reached minimum payout.

The payouts were stored in a separate table from the users and users could have multiple payment records prior to reaching payout, thus we would need to use a SUM() on the amount.  Because we also required other data, we need to use a group by to properly sum the amounts a user has.

The task was handed back to me several hours later to review, it was quite evident that the main concept was “grasped”, but the understanding of group bys was not. More »

My pet peeve list seems to grow on a regular basis.  One of them is poorly optimized code.  Not only is the code I’m going to discuss poorly optimized, it’s from the documentation of CakePHP!

If you search for creating custom pagination in CakePHP, you will find quite a few results, potentially this one.  The one that I would assume the best would be the one from CakePHP itself, but it’s not and let me show you why. More »

I’ll continue today with another good database programming article.  If you missed yesterdays, you may view it here.

Today’s article will discuss when to use a static COUNT() versus a dynamic COUNT().  On a regular basis we are tasked with creating a message center or a photo album and we need to display a count of new messages or pictures in the album.

The obvious solution is to do a simple query: SELECT COUNT(*) FROM pictures WHERE album_id = 1 and display the result beside our album name.  Now, let’s assume that we allow multiple albums our actual query would be closer to this: SELECT albums.*, COUNT(pictures.*) as picture_count FROM albums INNER JOIN pictures ON albums.id = pictures.album_id WHERE albums.id = 1.  This will of course work, but let me show you a better, more practical approach that will improve performance significantly on large databases. More »