SQL : Insert or Update multiple row at one time with condition

Question

To understand what I'd like to achieve, here is my problem :

• I have a list of "Place" in php array + sql table name "Place"
• Each "Place" can have one or multiple "Place_Box"
• Each "Place_Box" have (or not) some "Slot_Place_Box"
• This "Slot_Place_Box" have field "date", "hour", "slot_available"
• Now I have a list of "Date" + a list of "Hour" in some php arrays (some date have hour, other have not)
• I have a "Type" var in php ("1", "2" or "3")

What I want to do :

• I need to check if for each "Place", for each "date"/"hour" I already have a "Slot_Place_Box" in database :
• If YES : I update "slot_available" -> current data - Type
• If NO : I create a new "Slot_Place_Box" -> date + hour + slot_available = 3 - type
• THEN : I have to get the last ID (the new I created or the one I updated) and create a new "Slot_config" data with this ID + an other ID I have in php var.

Is it possible to achieve this in ONE Script/Request, using only MySQL and php? I just want to have few repetion and avoid to make request in php foreach loop...

• I saw this anwser to do the "update or insert" part : link
• I saw this one for the "insert multiple row in one query" part : link

But I have doubt that I can combinate the two...I'd like to do something like this but I'm almost sure this is wrong and I will have to make at least one request by "Place" :

INSERT INTO Slot_Place_Box ( Date, Hour ) VALUES
    ( "2017-11-01", "23:00:00" )
ON DUPLICATE KEY UPDATE
    Date = "2017-11-01", Hour = "23:00:00"
LEFT JOIN "Place_Box" as PB ON PB.id_place_box = Slot_Place_Box.id_place_box
INNER JOIN "Place" as P ON P.id_place = PB.id_place

@id = SELECT LAST_INSERT_ID() or @id = (SELECT id FROM slot_place_box WHERE date = "2017-11-01" AND hour = "23:00:00"

INSERT INTO "Slot_config" (id_slot_pb, id_config) VALUES (@id, :id_config)

I hope my problem is clear enough to understand what I'd like to achieve, I'm beginner in SQL so if you just have some "logic" to help me make my script it would be very nice :)

EDIT 1 :

After one more day working on this, this is how I think I will do it :

• I get every "id_place_box" according to my "Place" data
• Then I will create an array with all the combinaison possible of "id_place_box" / "date" / "hour"
• Then I will make a "SELECT" of all the "slot_place_box" where I can find a combination of id_place_box / date / hour -> I get "slot_available" + "id_slot_pb" value, then I UPDATE every result and keep the id's in an array.
• I now can make an INSERT IGNORE new "slot_place_box" with all the combination "id/date/hour" and add every new id to my id's array.
• Then I create a new "slot_config" foreach id in my array

I think it should works like this

EDIT 2 :

I'm not sure I can do what I wanted to achieve (insert or update multiple row at once) for the following reason :

• I can't really UPDATE the "slot_available" field on "Slot_place_box" with some calcul, I need to get the current value, then find the new value according to my "type" value : I can't do

  INSERT INTO Slot_Place_Box ( id_place_box, Date, Hour ) VALUES
    ( :id_place_box_1, :date_1, :hour_1 ), 
    ( :id_place_box_2, :date_2, :hour_2 ), 
    ...
    ( :id_place_box_n, :date_n, :hour_n )n
  ON DUPLICATE KEY UPDATE slot_available = [some calcul]
  

• The other problem is that I can't get all the id's I UPDATED or INSERTED if I do all in one request. After each update or insert I need to keep the id somewhere.

Answer 1

I don't know if it's the best solution but I got the result I wanted. I loop throught my array with "id_pb / date / hour" data and foreach row I create a new request :

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)
                         VALUES ('. $current_id_pb .', \''. $current_date .'\', \''. $current_hour .'\')
                         ON DUPLICATE KEY UPDATE id_slot_pb=id_slot_pb;
                         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_slot_pb=id_slot_pb;
                         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);
                         ';
    }
}
$sql_slot_pb = $bdd->prepare($sql_slot_pb);
$sql_slot_pb->bindParam(':new_config_id', $new_config_id);
$sql_slot_pb->execute();

I got the "$new_config_id" before when I create INSERT my config data then I made an SQL trigger to UPDATE the "slot_available" field :

CREATE DEFINER= ***** TRIGGER slot_config_AFTER_INSERT AFTER INSERT ON `slot_config` FOR EACH ROW
BEGIN
SET @type_config = 
    (SELECT CC.type 
    FROM config_campaign   as CC
    INNER JOIN slot_config as SC ON SC.id_config = CC.id_config 
        AND SC.id_slot_pb = NEW.id_slot_pb
        AND SC.id_config = NEW.id_config
    );

IF @type_config = 1
    THEN SET @value_type = 3;
ELSE
    IF @type_config = 2
        THEN SET @value_type = 1.50;
    ELSE
        IF @type_config = 3
            THEN SET @value_type = 1.00;
        END IF;
    END IF;
END IF; 

SET @current_slot_available = 
    (SELECT SPB.slot_available
    FROM slot_place_box as SPB
    WHERE SPB.id_slot_pb = NEW.id_slot_pb
    ); 

UPDATE slot_place_box SET slot_available = (@current_slot_available - @value_type) WHERE id_slot_pb = NEW.id_slot_pb;
END