4

To begin with, here is my code structure and what I want to achieve :

I have this 3 tables :

+----------------------+
| CONFIG_CAMPAIGN      |
+----------------------+
| - id_config          | 
| // other fields      |
+----------------------+

+----------------------+
| SLOT_CONFIG          |
+----------------------+
| - id_slot_config     | 
| - id_config          |
| - id_slot_pb         |
+----------------------+

+----------------------+
| SLOT_PLACE_BOX       |
+----------------------+
| - id_slot_pb         | 
| - id_place_box       |
| - date               |
| - hour               |
| - slot_available     |
+----------------------+

So one "slot_place_box" can have multiple "config" and one "config" can be on multiple "slot_place_box". I have some trigger on "slot_config" to update the field "slot_available" according to the config.

Now I have a PHP where I get some FORM data and build a SQL request which look like this (when I have my problem) :

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-10', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box;
SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-10' AND SPB.hour IS NULL);
INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id);

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-11', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box;
SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-11' AND SPB.hour IS NULL);
INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id);

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-12', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box;
SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-12' AND SPB.hour IS NULL);
INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id);

// and so on

The idea is to create a new "slot_place_box" + "slot_config" if I have no duplicate according to the new config I create before in my code

OR

If I already have a "slot_place_box" with same "id_place_box + date + hour" I only INSERT a new "slot_config" with the new_config id + the "slot_place_box" I already have.

I choose the "INSERT...ON DUPLICATE KEY /do nothing/" because I need the id of the row I INSERT / I already have.

I have an UNIQUE index key in "slot_place_box" for the fields "id_place_box + date + hour". My problem begin because "hour" can be an hour ("12:00:00" for example) but can be NULL too (= for me, when NULL it means "all day" of the "date"). When hour is NULL, my UNIQUE index doesn't work so the INSERT happens and I have duplicate in database.

So how can I make this "INSERT...ON DUPLICATE KEY UPDATE" works with an UNIQUE index with NULL value?

I can't use this solution (here) because I use MySQl 5.6 so I will try to make a trigger as someone suggest me in my last question (here).

I'm beginner in SQL so I'd like some help please to achieve this trigger please. The idea, if I understand well, is to create some "virtual" column with the trigger and add some value in it if "hour" is null + add this virtual column in my UNIQUE index so the ON DUPLICATE KEY will works.

So my questions are :

  • How can I create this "virtual column" in a trigger?
  • How can I use this "virtual column" for my UNIQUE index if I need the "hour" value when it's not NULL?
  • Do you have a better idea?
Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36

1 Answers1

1

I will anwser myself but if you guys have other solution I'm still interested to heard it. This solution works for me because I'm working on a new project so it don't really impact the code I already wrote.

To workaround this problem, I add a new column to my "slot_place_box" table :

+----------------------+
| SLOT_PLACE_BOX       |
+----------------------+
| - id_slot_pb         | 
| - id_place_box       |
| - date               |
| - hour               |
| - slot_available     |
| - virtual_hour       | <- new field
+----------------------+

This new field is a VARCHAR(10) equal to "hour" if not NULL (ex: "12:00:00") or equal to "1" by default or when "hour" is NULL.

Then I change my UNIQUE index on this table for "id_place_box + date + virtual_hour" so I will never get NULL value. Then I UPDATE the data I already have in my table :

UPDATE slot_place_box SET (virtual_hour = IF(hour is NULL, '1', hour);
// Without the safe UPDATE (I use MySQL WorkBench)

I change my PHP loop to build my request with the new column:

 foreach($SPB_data_array as $index => $SPB_data) {
    $current_id_pb = $SPB_data['id_pb'];
    $current_date  = $SPB_data['date'];

    if (!empty($SPB_data['hour'])) {
        $current_hour = $SPB_data['hour'];
        $sql_slot_pb .= 'INSERT INTO media.slot_place_box (id_place_box, date, hour, virtual_hour) VALUES ('. $current_id_pb .', \''. $current_date .'\', \''. $current_hour .'\', \''. $current_hour .'\')
                            ON DUPLICATE KEY UPDATE id_place_box=id_place_box;
                         SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB
                            WHERE SPB.id_place_box = '. $current_id_pb .' AND SPB.date = \''. $current_date .'\' AND SPB.hour = \''. $current_hour .'\');
                         INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (:new_config_id, @id);
                         ';
    } else {
        $sql_slot_pb .= 'INSERT INTO media.slot_place_box (id_place_box, date, hour) VALUES ('. $current_id_pb .', \''. $current_date .'\', NULL)
                            ON DUPLICATE KEY UPDATE id_place_box=id_place_box;
                         SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB
                            WHERE SPB.id_place_box = '. $current_id_pb .' AND SPB.date = \''. $current_date .'\' AND SPB.hour IS NULL);
                         INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (:new_config_id, @id);
                         ';
    }
}

I just added the fourth column with the "hour" value when "hour" is NOT NULL, by default "virtual_hour" is "1" so no need to add it in this case.

I don't know if it's the best solution for BIG project with lot of data, but in my case it was the easiest way to resolve my problem.

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36