我的目標是使用 CodeIgniter 的組合insert_batch()并將update_batch()傳入數(shù)據(jù)添加到我的macro_plan表中。在我下面的腳本中，我試圖根據(jù)sr_no值查詢數(shù)據(jù)庫中的現(xiàn)有行，然后適當?shù)卣{(diào)用批量查詢方法。function insert_batch($dataSet){ $query = $this->db->query("select sr_no from macro_plan"); $data = $query->result_array(); $sr_nos=array(); foreach($data as $key => $value): $sr_nos[$key]=$value['sr_no']; endforeach; $query1= $this->db->query("select * from macro_plan WHERE sr_no IN ('".$sr_nos."')"); $update_query = $query1->result(); if ($update_query->num_rows() > 0) { $this->db->update_batch($dataSet,$this->macro_plan);//update if ids exist } else { $this->db->insert_batch($dataSet,$this->macro_plan);//insert if does not exist }}但是，我收到“數(shù)組到字符串轉(zhuǎn)換”錯誤。$dataset將類似于：Array ( [0] => Array ( [quantity_update] => 88 [sr_no] => 2020-11-1 [batch] => Batch 2 [quantity_date_update] => 05-May-20 [inq_id] => 49 ) [1] => Array ( [quantity_update] => 99 [sr_no] => 2020-11-2 [batch] => Batch 1 [quantity_date_update] => 11-May-20 [inq_id] => 49 ))我的表結(jié)構(gòu)如下所示：
1 回答
慕絲7291255
TA貢獻1859條經(jīng)驗 獲得超6個贊
在您的表中查詢包含
sr_no您的$dataSet.然后將鍵應用于值中的結(jié)果集行
sr_no——這允許根據(jù)舊數(shù)據(jù)快速查找新數(shù)據(jù)（以查看是否應插入相應的新行、作為更新執(zhí)行或完全忽略，因為數(shù)據(jù)是相同的。
未經(jīng)測試的建議：
function insertUpdateMacroPlan($dataSet)
{
$keyedExistingRows = array_column(
$this->db
->where_in('sr_no', array_column($dataSet, 'sr_no'))
->get('macro_plan')
->result_array(),
null,
'sr_no'
);
foreach ($dataSet as $data) {
if (isset($keyedExistingRows[$data['sr_no']])) {
// sr_no exists in the db, add known id to new data array
$identified = ['id' => $keyedExistingRows[$data['sr_no']]['id']] + $data;
if ($identified != $keyedExistingRows[$data['sr_no']]) {
$updateBatch[] = $identified;
}
// if the arrays contain the same data, the new data will be discarded
} else {
$insertBatch[] = $data;
}
}
if (!empty($insertBatch)) {
$this->db->insert_batch('macro_plan', $insertBatch);
}
if (!empty($updateBatch)) {
$this->db->update_batch('macro_plan', $updateBatch, 'id');
}
}
ps 如果您的業(yè)務邏輯要求sr_no值是唯一的，我建議您通過將sr_no列設置為唯一鍵來反映在您的表配置中。
- 1 回答
- 0 關注
- 158 瀏覽
添加回答
舉報
0/150
提交
取消