Executing multiple queries in codeigniter that can

问题:

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/)

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)

$data is an array that user has filled.

---

Problem 1:

I couldn't execute $query with $this->db->query($query);;

Solution 1 that didn't work:

I. Use mysqli db engine.

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.

---

Problem 2:

$this->db->insert_id() doesn't work (returns 0)

Problem 3:

$this->db->update('event', $data); errors: 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.

回答1:

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

回答2:

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 = "CALL some_sp($param1, $param2...etc)";

回答3:

User-defined variables in MySQL are connection-specific and are kept until you close the connection.

$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();
}

回答4:

$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);