Multi statement query mySQL / PHP

Question

This is my first attempt to run a query with PHP on mySQL database. Consider I have a valid MDB2 db object created like this: $db = MDB2::connect(....

I want to execute a query ($query) like this:

SET @node_id := (SELECT cp_node_id FROM ilias.cp_item AS T1 WHERE id="ITEM-2008-11-28-8-6-5-281");
SET @id := (SELECT cmi_node_id FROM ilias.cmi_node AS T2 WHERE user_id=189 AND cp_node_id=@node_id);
SET @id = ifnull(@id, (SELECT MAX(cmi_node_id) FROM ilias.cmi_node AS T2)+1);
SET @id = ifnull(@id, 0);

INSERT INTO ilias.cmi_node
(cmi_node_id, user_id, cp_node_id, completion_status,
learner_name, progress_measure, success_status, scaled, c_timestamp)
VALUES
(@id, 189, @node_id, "completed",
"Luca Viggiani", 1.0, "passed", NULL, NOW())

ON DUPLICATE KEY UPDATE
completion_status=VALUES(completion_status),
learner_name=VALUES(learner_name),
progress_measure=VALUES(progress_measure),
success_status=VALUES(success_status),
c_timestamp=VALUES(c_timestamp);

Assuming that the above text is in $query, I tryed

$db->exec($query);

or

$db->extended->executeMultiple($query);

but none works.

Please consider that the above query works fine in MySQL Workbench. Also please consider thsat running a simple (single statement) query from PHP like this, it works fine too:

$db->exec("INSERT INTO ilias.cmi_node (cp_node_id) VALUES (5);");

EDIT: Also a simpler (two statements) query like this fails in PHP and works in MySQLWOrkBench:

$query2 = "SET @node_id := (SELECT cp_node_id FROM ilias.cp_item AS T1 WHERE id='ITEM-2008-11-28-8-6-5-281');".
                  " INSERT INTO ilias.cmi_node (cp_node_id) VALUES (@node_id);";

Well it's not easy to me to debug as that code stays into a soap webservice consumed by java. BTW $db->exec gives me 1 as a result — lviggiani, Jan 16 '14 at 12:45

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

I think maybe MDB2 is somehow changing your query. so to find it out you need to log the queries being executed in your database(take a look at: http://dev.mysql.com/doc/refman/5.1/en/query-log.html and How to show the last queries executed on MySQL?).

edited May 23 '17 at 11:57

Community

1
1

answered Jan 16 '14 at 12:38

Soosh

812
1
8
24

score 0 · Accepted Answer · answered Jan 16 '14 at 13:32

0

...ok I found out what was the problem: I cannot execute multiple sentences in one go. I had to split them into single sentences and then execute them sequentially like this:

$statements = explode(";", $query);

foreach($statements as $stat)
  if (isset($stat) && $stat!=='') $db->exec($stat);

answered Jan 16 '14 at 13:32

lviggiani

5,824
12
56
89

but this makes overhead unless you commit the queries at the end. – Soosh Jan 16 '14 at 21:15
Do you mean after the foreach loop or after each $db->exec() ? – lviggiani Jan 17 '14 at 07:54

Multi statement query mySQL / PHP

2 Answers2