Home>

I want to UPDATE if $id and $active_datetime_str already exist, INSERT if they don't exist. Even if the following code is executed, it always inserts. Thanks for your teaching.

CREATE TABLE `schedule` (
  `id` varchar (20) NOT NULL,
  `datetime` datetime NOT NULL,
  `schedule` tinyint (1) NOT NULL
)
function update_schedule ($dbh, $id, $set_schedule) {
$active_datetime_str = get_active_datetime_str ();
$cnt = count ($active_datetime_str);
for ($i = 0;$i<$cnt;$i ++) {
$sql =<<<SQL
INSERT INTO `schedule`
VALUES (: id,: active_datetime_str,: set_schedule)
ON DUPLICATE KEY UPDATE schedule = VALUES (schedule)
SQL;
// PDO :: PARAM_STR->Prepared statement
$stmt = $dbh->prepare ($sql);
// assign variable to SQL
$stmt->bindValue (': id', $id, PDO :: PARAM_STR);
$stmt->bindValue (': active_datetime_str', $active_datetime_str [$i], PDO :: PARAM_STR);
$stmt->bindValue (': set_schedule', $set_schedule [$i], PDO :: PARAM_STR);
if (! $stmt->execute ()) {
return FALSE;
}
}
return TRUE;
}
  • Answer # 1

    First of all, do you have a unique attribute for datetime?
    $Id doesn't change with $i, is it okay?
    To check datetime with duplicate $active_datetime_str [$i]
    datetime= VALUES (datetime)
    I do not know what I want to do
    And the prepare process is totally wrong

    Executing SQL one sentence at a time with infor names is inefficient.
    I recommend. (Adjustment is required to process tens of thousands)

    sample

    Table creation and insert

    create table `schedule` (
      `id` varchar (20) NOT NULL,
      `datetime` datetime NOT NULL,
      `schedule` tinyint (1) NOT NULL,
      unique key (`id`,` datetime`)
    );
    insert into `schedule` values ​​('honda', '2018-09-08 10:00:00', 1);

    Table creation and insert

    insert into `schedule` values
    ('kagawa', '2018-09-08 10:00:00', 2),
    ('honda', '2018-09-09 10:00:00', 3),
    ('honda', '2018-09-08 10:00:00', 4)
    on duplicate key update schedule = values ​​(schedule)

    The data of 2 is inserted because the id is different

    Data 3 is inserted because datetime is different

    The data of 4 is updated with the specified 4 because it has id and datetime