+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: ON DUPLICATE KEY or equivalent (/showthread.php?tid=69476)
ON DUPLICATE KEY or equivalent - colin147 - 11-29-2017
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);
}
}RE: ON DUPLICATE KEY or equivalent - dave friend - 11-29-2017
Have you looked into the Query Builder method replace()? (Docs here)
Or, you can include a hidden field to hold the value of the primary key field when updating a record or, for a new record the value is NULL
If ($_POST['your_key_field'] === NULL) then insert, else, update.
RE: ON DUPLICATE KEY or equivalent - colin147 - 11-30-2017
Thanks. Yeah I don't want to use replace as that will create a new auto-increment PK.
The PK isn't the issue, it's a unique index on 3 fields, but I'll see if your idea of seeing if it is there and then acting accordingly would work.
Cheers
RE: ON DUPLICATE KEY or equivalent - colin147 - 11-30-2017
Thanks again, very simple when you stop to think about it.
It's still not working, but not because of this!
RE: ON DUPLICATE KEY or equivalent - keebOo - 12-07-2017
(11-29-2017, 12:54 PM)colin147 Wrote: 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);
}
}
I had a similar problem and this is my solution... maybe could be useful also for you?
PHP Code:
public function update_limit_edit($struttura_id,$data_limit)
$sql =('
INSERT INTO mesi_chiusi (struttura_id, data_limit, upd_by_user) VALUES(?,?,?)
ON DUPLICATE KEY UPDATE
data_limit =?,
upd_by_user =?
');
$query = $this->db->query($sql,array(
$struttura_id,$data_limit,$this->ion_auth->user()->row()->id,
$data_limit,$this->ion_auth->user()->row()->id));