Jump to content

TIP: Mixing columns with IFs and other IF fun


Recommended Posts

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:
  userID user,
  IF (income>=50000, 'rich' , 'poor') status
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:

  IF (pointsEarned > maxPointsAllowed , maxPointsAllowed, pointsEarned)
from game;
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).

select userID,
    mins > 5,
        mins >10,
          mins >15,
          'more than 15 mins',
          '10-15 mins'
    'less than 5mins'
    ) time_spent
from users;

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.