How to do an IF THEN Statement in MySQL?

Question

MysQL (table1):

+----+--------+-------+--------+
| id | itemid | title | status |
+----+--------+-------+--------+
| 1  |   2    | title |   0    |
+----+--------+-------+--------+
| 2  |   2    | title |   1    |
+----+--------+-------+--------+
| 3  |   3    | title |   1    |
+----+--------+-------+--------+
| 4  |   3    | title |   0    |
+----+--------+-------+--------+
| 5  |   3    | title |   0    |
+----+--------+-------+--------+

MySQL (table2):

+----+---+---+
| id | x | y |
+----+---+---+
| id | 1 | 2 |
+----+---+---+

PHP:

(I know the query below makes no sense, but it should just illustrate what I am trying to do here.)

$a = mysql_query("SELECT t1.title FROM table1 AS t1 WHERE t1.title = 'title' ...IF t1.status = 1 THEN (SELECT * FROM table2 AS t2 WHERE t2.x = '1' AND t2.y = t1.itemid) ORDER BY `id`");
while($b = mysql_fetch_assoc($a))
{
    echo $b['title'];
}

So, what I want to do is:

1) Get from table1 all rows that match title = title

2) If the status in table1 is equal to 0 do nothing, just display the data

3) However, if the status in table1 is equal to 1 then it should check if there is a record in table2 where x = 1 and y = itemid (from table1), if there isn't than the data from table1 should be excluded

Example: In the example above, it should display ids (table1): 1, 2, 4, 5 ...3 should be excluded, because the status is 1, and there is no record that matches in table 2.

I hope this makes sense :/

score 4 · Accepted Answer · answered May 07 '11 at 17:43

4

you should use join for this.

Mysql join

or have a look how to use control flow functions in select statements:

Control flow functions

answered May 07 '11 at 17:43

Headshota

21,021
11
61
82

here's the answer. using a join adds the data if there is any, otherwise it won't be send along in the resultset. – Joshua - Pendo May 07 '11 at 17:46

score 0 · Answer 2 · answered May 07 '11 at 17:48

I would just like to assert that if you've got any if-then-else logic, any further processing of the data that you have stored in a database, use a programming language for that. There's nothing stopping you from writing all this logic in PHP, Java, C#, etc. There are far too many questions here about if-then-else stuff when retrieving data from database.

Some database management systems have their own dialect for programmatic SQL, be it PL/SQL or T-SQL for Oracle/SQL Server respectively... You could create your own modules with logic (packages, stored procedures) in your database and then use those, but then... do you really need to? Why could you not just implement all the data-presentation logic in your PHP script?

score 0 · Answer 3 · edited May 23 '17 at 11:55

I'd like to expand on the previous answers a bit with some conceptual background: remember that SQL is declarative, not procedural. What this means is that you basically use SQL to tell the database what you want your result table to look like, and it figures out how to give it to you. When you're thinking about if/then statements and control logic, you're thinking in procedural terms, not declarative terms. This is why the previous answerer is suggesting to do your if/else logic in a 'programming language' like C# or PHP.

That's pretty abstract and so might not be directly applicable for you right this moment, but I find it helpful to compartmentalize things into 'procedural' and 'declarative' when I'm working with SQL and a scripting language (like PHP).

This StackOverflow question has some pretty good answers on the procedural vs. declarative concept.

How to do an IF THEN Statement in MySQL?

3 Answers3