CODEWITHSUNDEEP
mysqlinsert
GustavoxD
GustavoxD

Reputation: 89

MySQL - INSERT command with conditions

I have a table called eleva with the columns inicio_periodo, id, and i want to populate another table with some data from this one, when a condition is satisfied. I'm trying the where statement that should work, but it inserts on the other table regardless of the condition, here's my code:

for($i=0; $i < $tamanho_array_eleva-1; $i++) {
    if($array_eleva[$i+1] - $array_eleva[$i] > 1){
        $con->query("
                    INSERT INTO eventos_dias
                    SELECT DATE(inicio_periodo),'1','0','0','0'
                    FROM eleva
                    WHERE id = $array_eleva[$i] AND 0<=HOUR(inicio_periodo)<6");
        $con->query("
                    INSERT INTO eventos_dias
                    SELECT DATE(inicio_periodo),'0','1','0','0'
                    FROM eleva
                    WHERE id = $array_eleva[$i] AND 6<=HOUR(inicio_periodo)<12");
        $con->query("
                    INSERT INTO eventos_dias
                    SELECT DATE(inicio_periodo),'0','0','1','0'
                    FROM eleva
                    WHERE id = $array_eleva[$i] AND 12<=HOUR(inicio_periodo)<18");
        $con->query("
                    INSERT INTO eventos_dias
                    SELECT DATE(inicio_periodo),'0','0','0','1'
                    FROM eleva
                    WHERE id = $array_eleva[$i] AND 18<=HOUR(inicio_periodo)<24");
    }
}

I know it isn't working because currently on the table i just have data with the hour 16... but it's executing all the inserts regardless. Does anyone see an error?

Just if helps: my idea is to populate the other table with the selected values if the condition number<=hour(inicio_periodo)<number is satisfied. Thanks in advance!

Upvotes: 0

Views: 107

Answers (1)

Wrikken
Wrikken

Reputation: 70460

THis query shows you why:

mysql> SELECT 1<= 16 < 6;
+------------+
| 1<= 16 < 6 |
+------------+
|          1 |
+------------+

As it's

mysql> SELECT (1 <= 16) < 6;
+---------------+
| (1 <= 16) < 6 |
+---------------+
|             1 |
+---------------+

Which becomes:

mysql> SELECT 1 < 6;
+-------+
| 1 < 6 |
+-------+
|     1 |
+-------+

Likewise: 18 <= 16 < 24 => (18 <= 16) < 24 => 0 < 24 => 1

In short, use value BETWEEN min AND max, that's what it's for, or use separate checks:

mysql> SELECT 16 BETWEEN 1 AND 6;
+--------------------+
| 16 BETWEEN 1 AND 6 |
+--------------------+
|                  0 |
+--------------------+

 mysql> SELECT 1 <= 16 AND 16 < 6;
+--------------------+
| 1 <= 16 AND 16 < 6 |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions