php 使用 CodeIgniter 更新批处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6264955/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update batch with CodeIgniter
提问by ludovic
I'm trying to do a small open CMS with CodeIgniter and I'm now working on the categories system.
我正在尝试用 CodeIgniter 做一个小的开放式 CMS，我现在正在研究分类系统。
I'm really stuck with that and after many tries and forum post I didn't solve it.
我真的坚持这一点，经过多次尝试和论坛帖子，我没有解决它。
I have 2 mySQL TABLES
我有 2 个 mySQL 表
- #1 : ft_categories (list all categorie's names with 2 fields : category_name and id)
- #2 : ft_upload_data (list all posts with fields like id, title, category, date and so on)
- #1 : ft_categories（列出所有类别的名称，包含 2 个字段：category_name 和 id）
- #2 : ft_upload_data（列出所有带有 id、title、category、date 等字段的帖子）
I want to UPDATE my #1 TABLE with the datas in an edit categorie's names form (this form is filled with inputs in a loop to edit multiple categories at once)
我想用编辑类别名称表单中的数据更新我的#1 TABLE（此表单在循环中填充输入以一次编辑多个类别）
Here it is :
这里是 ：
if ($result != NULL) {
echo form_open('admin/update_categories/');
if (isset($result) && $result != NULL) {
foreach ($result as $row) {
echo form_input('category_name[]' ,$row->category_name);
echo anchor("admin/delete_category/$row->category_name", 'Delete category');
echo '<br /><br />';
}
echo '<br /><br />';
echo form_submit('','Save');
echo form_close();
} } else { echo 'NO categories'; }
This is the form with the inputs retrieved from the DB where you can edit the name.
这是带有从数据库中检索到的输入的表单，您可以在其中编辑名称。
Ok now when you edit the categorie's names you go to the 'update_categories' CONTROLLER to do the UPDATE request
好的，现在当您编辑类别名称时，您会转到“update_categories”控制器来执行 UPDATE 请求
function update_categories(){
$i = 0;
foreach ($this->input->post('category_name') as $cat)
$data[$i++]['category_name'] = $cat;
// The $i++ creates a multi-dimensional array to insert
// multiple rows into the DB.
$this->admin_model->update_categories($data);
}
This will get the multiples inputs fields to UPDATE the DB. And now I go to the MODEL to UPDATE the data in the DB and HERE'S THE PROBLEM :
这将获得多个输入字段以更新数据库。现在我去模型更新数据库中的数据和这里的问题：
function update_categories($data) {
?
I don't know what can I do to update the names correctly with something like an insert_batch but with UPDATE because although I need to UPDATE the TABLE #1, I also NEED to update the name in the field in the TABLE #2 Double UPDATE on 2 Tables and 1 batch UPDATE in the TABLE #1
我不知道我该怎么做才能使用 insert_batch 之类的东西正确更新名称，但使用 UPDATE 因为虽然我需要更新 TABLE #1，但我还需要更新 TABLE #2 中字段中的名称 Double UPDATE表 #1 中的 2 个表和 1 个批量更新
Obviously I tried to add one more TABLE : TABLE #3 which get the TABLE#1 field id and match it with the TABLE#2 field id but I can't figure it out to do connection between the 3 tables.
显然，我试图再添加一个 TABLE : TABLE #3，它获取 TABLE#1 字段 id 并将其与 TABLE#2 字段 id 匹配，但我无法弄清楚在 3 个表之间进行连接。
Any help would be very very appreciated! Many thanks!! (sorry for my bad english)
任何帮助将不胜感激！非常感谢！！（对不起，我的英语不好）
Thanks for answer!
谢谢解答！
Ok I have this third table now I would like to retrieve the 'category_name' to show this within the 'post'
好的，我现在有第三张表，我想检索“category_name”以在“post”中显示
I have this :
我有这个 ：
$this->db->order_by('rank', 'asc');
$this->db->select('*');
$this->db->from('ft_upload_data');
$this->db->join('ft_categories', 'assigned_categories.ft_categories_id = assigned_categories.ft_upload_data_id');
$query = $this->db->get();
return $query->result();
But it says there is Unknown column 'assigned_categories.ft_categories_id' in 'on clause'
但它说'on 子句'中有未知列'assigned_categories.ft_categories_id'
(assigned_categories is my third TABLE with the post id and the category id matched)
（assigned_categories 是我的第三个 TABLE，帖子 ID 和类别 ID 匹配）
Any idea?
任何的想法？
回答by Kanishka Panamaldeniya
try to use UPDATE_BATCH
尝试使用 UPDATE_BATCH
$this->db->update_batch();
$data = array(
array(
'title' => 'My title' ,
'name' => 'My Name 2' ,
'date' => 'My date 2'
),
array(
'title' => 'Another title' ,
'name' => 'Another Name 2' ,
'date' => 'Another date 2'
)
);
$this->db->update_batch('mytable', $data, 'title');
The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.
第一个参数将包含表名，第二个是值的关联数组，第三个参数是 where 键。
hope this help........................
希望这有帮助......................
UPDATE
// Produces:
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')
回答by Jarek Tkaczyk
First you cannot update multiple rows in one query, so there's nothing like insert_batch() for updates - you have to do it in a loop for each row. Next you should use 3rd table to link post id with category id in a one to many relation using foreign keys (InnoDB, worse performance) OR at least use category_id field in TABLE#2 that links to the category id, so you don't need to update this table in case of changes in TABLE#1.
首先，你不能在一个查询中更新多行，所以没有什么比 insert_batch() 更新 - 你必须在每行循环中进行。接下来，您应该使用第 3 个表使用外键（InnoDB，性能较差）以一对多关系将帖子 ID 与类别 ID 链接，或者至少使用表#2 中的 category_id 字段链接到类别 ID，这样您就不会如果 TABLE#1 发生变化，需要更新此表。
