更新一行,但如果代码点火器中不存在该行,则进行插入 [英] Update a row, but insert if row doesn't exist in codeigniter
问题描述
我想在表格中插入一行,就像这样:
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
我想我可以进行 INSERT IGNORE INTO ,但是我更喜欢使用CodeIgniters upate_batch 和 insert_batch .
I guess I could do a INSERT IGNORE INTO but I would prefer using CodeIgniters upate_batch and insert_batch.
$update
-变量类似
$update = array(
array('id' => 1, 'name' => 'Gustav'),
array('id' => 2, 'name' => 'Peter'),
array('id' => 3, 'name' => 'Lisa')
)
更新
通过我得到的答案,我对自己想要的东西还不够清楚. (而且我认为我不够清楚我想要的是什么,所以这是我更新的问题,希望更清楚)
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
但是我想发生的事情
$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
我想它等于on key duplicate
对于insert_batch
.
推荐答案
首先从表中选择所有image_id.
$data = $this->db->select(`image_id`)->get($this->table_name)->result_array();
将image_id列出到数组中.
$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')
)
检查图像ID是否存在:
$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;
这篇关于更新一行,但如果代码点火器中不存在该行,则进行插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!