在codeigniter中执行无法一次一执行的多个查询 [英] Executing multiple queries in codeigniter that cannot be executed one by one
问题描述
我有一个事件"表.为简单起见,您可以想象它应该像一个分层类别.它使用嵌套集模型(感谢Mark Hillyer在 http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)
I have an "event" table. For simplicity, you can imagine that it should be like a hierarchical category. It uses the nested set model (Thanks to Mark Hillyer for his post at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)
我的代码:
$query =
"LOCK TABLE event WRITE;
SELECT @ParentRight := parent.rgt, @Level := parent.level FROM event AS parent WHERE parent.id = '{$data['parent_id']}';
UPDATE event SET rgt = rgt + 2 WHERE rgt > @ParentRight;
UPDATE event SET lft = lft + 2 WHERE lft > @ParentRight;
INSERT INTO event(lft, rgt, level) VALUES(@ParentRight, @ParentRight + 1, @Level);
UNLOCK TABLES;";
mysqli_multi_query($this->db->conn_id, $query);
$data['id'] = $this->db->insert_id();
return $this->db->update('event', $data);
然后,我将使用$this->db->update('event', $data)
$data
是用户已填充的数组.
$data
is an array that user has filled.
问题1:
我无法使用 $ this-> db-> query($ query); ;
无效的解决方案1:
I.使用 mysqli 数据库引擎.
I. Use mysqli db engine.
II. mysqli_multi_query($this->db->conn_id, $query);
虽然我认为它可行,但是却出现以下错误:
II. mysqli_multi_query($this->db->conn_id, $query);
While I thought it works, it is giving the following error:
Commands out of sync; you can’t run this command now.
问题2:
$this->db->insert_id()
不起作用(返回0)
问题3:
$this->db->update('event', $data);
错误:
Commands out of sync; you can't run this command now
如何更正此代码才能正常工作?我甚至很乐意为第一个问题找到解决方案.
How can I correct this code to work? I'd be even happy to find a solution for the first problem.
推荐答案
也许使用事务?
$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete();
https://www.codeigniter.com/user_guide/database/transactions.html
这篇关于在codeigniter中执行无法一次一执行的多个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!