Update a row, but insert if row doesn't exist

问题:

I want to do an insert of row in a table, like this:

$this->db->update_batch($this->table_name, $update, 'image_id');

if ($this->db->affected_rows() === 0) {
    $this->db->insert_batch($this->table_name, $update);
}

but if it exists I don't want to do anything. But this code above inserts another row because no row is affected

I guess I could do a INSERT IGNORE INTO but I would prefer using CodeIgniters upate_batch and insert_batch.

$update - variable is something like

$update = array(
   array('id' => 1, 'name' => 'Gustav'),
   array('id' => 2, 'name' => 'Peter'),
   array('id' => 3, 'name' => 'Lisa')
)

UPDATE

By the answers I get that I wasn't clear enough in what I wanted. (and I didn't think I was clear enough what I wanted so here is my updated question which I hope is clearer)

//This will insert Gustav, Peter and Lisa

$update = array(
   array('image_id' => 1, 'name' => 'Gustav'),
   array('image_id' => 2, 'name' => 'Peter'),
   array('image_id' => 3, 'name' => 'Lisa')
)
$this->db->insert_batch($update);


//If I do this it would insert Party Gustav, Peter and Lisa.
$update = array(
   array('image_id' => 1, 'name' => 'Party Gustav'),
   array('image_id' => 2, 'name' => 'Peter'),
   array('image_id' => 3, 'name' => 'Lisa')
)
$this->db->insert_batch($update);

//Above will create 6 rows

BUT what I want to happen IS

$update = array(
   array('image_id' => 1, 'name' => 'Gustav'),
   array('image_id' => 2, 'name' => 'Peter'),
   array('image_id' => 3, 'name' => 'Lisa')
)
$this->db->insert_batch($update);

$update = array(
   array('image_id' => 1, 'name' => 'Party Gustav'),
   array('image_id' => 2, 'name' => 'Peter'),
   array('image_id' => 3, 'name' => 'Lisa')
)


//Insert batch but when existing image_ids just change the name 
//from Gustav to Party Gustav (in this case)
$this->db->insert_batch($update); 

//Above will create 3 rows

I guess it will equal to something like on key duplicate for insert_batch.

回答1:

First Select all image_id from the table.

$data = $this->db->select(`image_id`)->get($this->table_name)->result_array();

List image_id into an array.

$image_ids=array();

foreach($data as $key => $value):

$image_ids[$key]=$value[`image_id`];

endforeach;

$update = array(
   array('image_id' => 1, 'name' => 'Party Gustav'),
   array('image_id' => 2, 'name' => 'Peter'),
   array('image_id' => 3, 'name' => 'Lisa')
)

Check if the image_ids exist:

$update_query= $this->db->where_in(`image_ids`,$image_ids)
               ->get($this->table_name)->result();

if($update_query->num_rows() > 0):

  $this->db->update_batch($update,$this->table_name);//update if ids exist
else
   $this->db->insert_batch($update,$this->table_name);//insert if does not exist
endif;

回答2:

I think what you're looking for is INSERT IGNORE INTO. Unfortunately, Codeigniter doesn't have an equivalent Active Record function. A discussion on the Codeigniter forums and this similar SO question suggest a method like this:

foreach ($update as $update_item) {
    $insert_query = $this->db->insert_string($this->table_name, $update_data['name']);
    $insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
    $this->db->query($insert_query);  
}

Also note that the image_id must be UNIQUE.

回答3:

It's been a while since I've used codeigniter, but could you check to see if the row exist first, and then either update or insert?