While working on a project, i got lazy (as i always do…); I didn’t feel like fetching an array of rows from MySQL and filtering the data in PHP according to a condition… Too much of a ballache to be honest. I thought: ‘Fuck it… Let’s see if one can use conditional statements in MySQL‘.

After doing a little research, i came across this page (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html). After scouring through the normally indecipherable MySQL reference manual, i managed to get the hang of it…

Although the scenarios where the application of this functionality are few and far between, i felt it’d still be a cool idea to share this (seemingly) arbitrary tit-bit.

Consider the following scenario…
You have a table of users. In this table, you keep the following information about each user:

  • name
  • surname
  • gender

Now, depending on the gender of your user, you would like to return their details with a certain prefix, namely Mr for a male or Ms for a female. Here’s the database structure, and two rows of sample data:

CREATE TABLE IF NOT EXISTS `users`
(
  `name` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `gender` enum('M','F') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
--
-- Sample data
--
 
INSERT INTO `users` (`name`, `surname`, `gender`) VALUES
('Joe', 'Shmo', 'M'),
('Jane', 'Shmane', 'F');

…and here’s the SQL query

SELECT *, CONCAT(IF(gender = "M", "Mr ", "Ms "), name, " ", surname)
AS fullName
FROM users

If that looks a little too indecipherable/complicated, here’s the previous query in a more simplified (albeit less efficient) way:

SELECT *, IF(gender = "M", CONCAT("Mr ", name, " ", surname),
			CONCAT("Ms ", name, " ", surname))
AS fullName
FROM users

conditionalsEssentially the IF() statement is structured like this:

IF(condition, true code, false code)

However, you can place an IF statement anywhere in a query (from what i can tell thus far), it gets executed in place and is replaced by either the true code or the false code.

Another (much simplified) example:

SELECT IF(1 > 0, "TRUE!", "FALSE!");

…and that’s conditionals with MySQL!