php 在codeigniter中执行多个查询无法一一执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8999959/
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
Executing multiple queries in codeigniter that cannot be executed one by one
提问by Mohammad Naji
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/)
我有一个“事件”表。为简单起见，您可以想象它应该像一个分层的 category。它使用嵌套集模型（感谢 Mark Hillyer 在http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ 上的帖子）
My code:
我的代码：
$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);
And after that I'm going to update the last inserted object with $this->db->update('event', $data)
之后我将更新最后插入的对象 $this->db->update('event', $data)
$datais an array that user has filled.
$data是用户已填充的数组。
Problem 1:
问题1：
I couldn't execute $querywith $this->db->query($query);;
我无法用$this->db->query($query);执行$query ; ;
Solution 1 that didn't work:
解决方案 1 不起作用：
I. Use mysqlidb engine.
一、使用mysqli数据库引擎。
II. mysqli_multi_query($this->db->conn_id, $query);While I thought it works, it is giving the following error:
二、mysqli_multi_query($this->db->conn_id, $query);虽然我认为它有效，但它给出了以下错误：
Commands out of sync; you can't run this command now.
Commands out of sync; you can't run this command now.
Problem 2:
问题2：
$this->db->insert_id()doesn't work (returns 0)
$this->db->insert_id()不起作用（返回 0）
Problem 3:
问题 3：
$this->db->update('event', $data);errors:
Commands out of sync; you can't run this command now
$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.
如何更正此代码以使其正常工作？我什至很乐意为第一个问题找到解决方案。
回答by webmaster_sean
Maybe use transactions?
也许使用交易？
$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
https://www.codeigniter.com/user_guide/database/transactions.html
回答by Skittles
Why not just write a stored procedure that does all the SQL you listed above taking the variables in your queries as parameters. Then just call the stored procedure as a single SQL statement;
为什么不编写一个存储过程来执行上面列出的所有 SQL，并将查询中的变量作为参数。然后只需将存储过程作为单个 SQL 语句调用；
$sql = "CALL some_sp($param1, $param2...etc)";
回答by sanmai
User-defined variables in MySQL are connection-specific and are kept until you close the connection.
MySQL 中的用户定义变量是特定于连接的，并且会一直保留到您关闭连接。
$queryList = array(
"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)",
false, // special value
"UNLOCK TABLES",
)
foreach ($queryList as $query) if ($query) {
mysqli_query($this->db->conn_id, $query);
// you should have error-checking here
} else {
// we have the special value
$data['id'] = $this->db->insert_id();
}
回答by Shailendra Singh Tiwari
$query1 = $this->db->query("SELECT * FROM `Wo_Products` WHERE `boost_plan`=1 AND `tamatar`=1 AND`active`=1 ORDER BY rand() LIMIT 3");
$query2 = $this->db->query("SELECT * FROM `Wo_Products` WHERE `boost_plan`=2 AND `tamatar`=1 AND`active`=1 ORDER BY rand() LIMIT 3");
$query3 = $this->db->query("SELECT * FROM `Wo_Products` WHERE `boost_plan` NOT IN (0,1) AND `tamatar`=1 AND`active`=1 ORDER BY rand() LIMIT 6");
$result1 = $query1->result();
$result2 = $query2->result();
$result3 = $query3->result();
return array_merge($result1, $result2, $result3);
