Handle 'Duplicate-entry error' in fat-free framework

Question

How to handle error when duplicate entries are inserted into table? I tried the following and it is not working.

function leaveBalanceRecord($f3)
{
    $sql = 
    "
    INSERT 
    INTO users_leave_balance (user_id, period, leave_type, leave_balance, rollable_till_date)
    VALUES (134, '2017-01', 1, 10.00, NULL)";
    $results = $this->db->exec($sql);
    if(!$results)
    {
        return 'Duplicate Entries';
    }
    return $results;
}

How to handle the error and update the record instead in case this error arises? Any help is appreciated. Thanks.

Answer 1

I see two solutions for your case:

1) Take benefit of the MySQL REPLACE INTO syntax:

$sql='REPLACE INTO users_leave_balance etc.';
$this->db->exec($sql);

From the docs:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

2) Catch the PDO exception:

$sql='INSERT INTO users_leave_balance etc.';
try {
  $this->db->exec($sql);
} catch (\PDOException $e) {
  $err=$e->errorInfo;
  if ($err[0]==23000) {
    // duplicate key: do something
  } else {
    // any other error
  }
}

See this answer for details on how to enable PDO exceptions.