Multiple query substitution in PHP / MySQL
As in PHP multiple queries are not supported using mysql_query(), how could I convert the following code to become a unique query ?
I precise that I can't change my database to MySQLi or anything else.
SELECT @myLeft := lft FROM hierarchie WHERE id = ".$father_id.";
UPDATE hierarchie SET rgt = rgt+2 WHERE rgt > @myLeft;
UPDATE hierarchie SET lft = lft+2 WHERE lft > @myLeft;
INSERT INTO hierarchie VALUES(".$cat.", ".$id.", '".$fullindi."', @myLeft+1, @myLeft+2);
Any help will be greatly appr开发者_如何学Pythoneciate.
Best regards.
UPDATE :
Answering to my own question, I found the link : http://www.dev-explorer.com/articles/multiple-mysql-queries where the author expalins how to do it.
For me, and following his explanations, the code becomes :
$sql = "
SELECT @myLeft := lft FROM hierarchie WHERE id = ".$father_id.";
UPDATE hierarchie SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE hierarchie SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO hierarchie VALUES(".$cat.", ".$id.", '".$fullindi."', @myLeft + 1, @myLeft + 2);
";
$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
foreach ($queries as $query){
if (strlen(trim($query)) > 0) mysql_query($query);
}
I've tested and it works well.
We can consider the problem is resolved, except if one of you has a better solution.
The solution is to use transactions so you can execute those queries separately but they will be considered as one.
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
So, for instance, the example above will require 4 mysql_query() commands but mysql will execute the whole thing as one query;
精彩评论