My table has a unique index; the primary key is auto increment.
I want to insert, but if there is a duplicate on the unique key, then update instead.
I'm using Active Record, and it keep tripping out on the Duplicate Key error, even though I'm trying to check the error code and if it's for duplicate (code 1062) insert instead, but it doesn't seem to be getting that far.
How can I resolve this? I don't want to use REPLACE as that would give me a new primary key via the auto increment.
Code:
$arr_match_info = array(
'fr_match_date' => $match_date,
'fr_season_lit' => $_POST['hdnSeasonLit_'.$side],
'fr_season_start' => $_POST['hdnSeasonStart_'.$side],
'fr_team_code' => $side,
'fr_compdiv' => $_POST['selComp_'.$side],
'fr_othercomp' => ($_POST['selComp_'.$side] != 'Other') ? '' : $_POST['txtOtherComp_'.$side],
'fr_opposition' => $_POST['selOppo_'.$side],
'fr_otheroppo' => ($_POST['selOppo_'.$side] != 'Other') ? '' : $_POST['txtOtherOppo_'.$side],
'fr_home_away' => $_POST['selHA_'.$side],
'fr_venue' => $arr_venue_info['name'],
'fr_alt_venue' => ($_POST['selVenue_'.$side] != 'Other') ? '' : $_POST['txtOtherVenue_'.$side],
'fr_mapref' => $arr_venue_info['mapref'],
'fr_kickoff' => $_POST['selKickoff_'.$side],
'fr_meet' => $_POST['selMeet_'.$side],
'fr_referee' => $_POST['txtReferee_'.$side],
'fr_assistants' => $_POST['txtAssistants_'.$side],
'fr_double_banker' => $double_banker,
'fr_postponed_flag' => isset($_POST['chkPostponed_'.$side]) ? 1 : 0,
'fr_postponed_reason' => isset($_POST['chkPostponed_'.$side]) ? $_POST['txtPostponedReason'.$side] : '',
'fr_instructions' => $_POST['txtInstructions_'.$side],
'fr_manager_contact' => $_POST['txtMgrContact_'.$side]
);
if($this->db->insert('fixtures_results', $arr_match_info))
{
// Success, move on to double banker question.
}
else //error
{
$error = $this->db->error();
if($error['code'] != 1062)
{
//serious error!!!
exit('Serious error on Insert');
}
else // duplicate index (match date, team code and double banker flag); just update instead.
{
$this->db->where('fr_match_date', $match_date);
$this->db->where('fr_team_code', $side);
$this->db->where('fr_double_banker', $double_banker);
$this->db->update('fixtures_results', $arr_match_info);
}
}
