Welcome Guest, Not a member yet? Register   Sign In
ON DUPLICATE KEY or equivalent
#1

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);
                }
            }
Reply
#2

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.
Reply
#3

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
Reply
#4

Thanks again, very simple when you stop to think about it.

It's still not working, but not because of this!
Reply
#5

(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)); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB