TIP: Mixing columns with IFs and other IF fun

[bibby]

I'm a big proponent of using PHP to handle your conditionals rather than making a query handle it, but you could!

MySQL IF statements take three arguments,  (condition, what to do if true , what to do if false)

Say you had a table with people and their income, and you wanted to export them as either rich or poor. Try this:
[code]
SELECT
  userID user,
  IF (income>=50000, 'rich' , 'poor') status
FROM
  users;
[/code]
Your export would have either have 'rich' or 'poor' for every user based on the number entered for income.


Here's another scenario where we could mix columns during a select...
Let's say there are users,  points earned, and the maximum allowable points.
Naturally, we'd take the lowest of the two point columns since points earned cannot exceed the maximum (but for data collecting reasons we didn't want to overide the points in php). Try:

[code]
select
  userID,
  IF (pointsEarned > maxPointsAllowed , maxPointsAllowed, pointsEarned)
from game;
[/code]
So if a record's pointsEarned is higher than the maximum, the maximum is used.
If the pointsEarned is lower, than the pointsEarned is chosen.
(if they are equal it doesn't really matter)
If we had many users, the second column of our export could easily be a mix of pointsEarned for some, and maxPointsAllowed for others.

You could easily nest IFs as well.
Recently I was asked to collect time on a time scale rather than straight integers, but I insisted on collecting raw integers knowing that I could parse it out later (and change it if I needed to).

[code]
select userID,
  IF(
    mins > 5,
    IF(
        mins >10,
        IF(
          mins >15,
          'more than 15 mins',
          '10-15 mins'
          ),
        '5-10mins'
        ),
    'less than 5mins'
    ) time_spent
from users;
[/code]

time_spent , even though they are stored as ints, are exported as timespans that meet at least one of these conditions.
And that's fun with IFs.