Returns false when running this query

Question

Any reasons why this not work? When I print the query to screen and runs it through phpMyAdmin it works. I left out the part where I connect to the database (MySQL).

$query = "START TRANSACTION; ";
$query .= "INSERT INTO table1(text) VALUES('$question_description'); ";

for ($i = 0; $i < count($processed_answers); $i++) {
    $query .= "INSERT INTO table2(question_id, text, serial_number, is_correct) ".
              "VALUES($question_id, '".$processed_answers[$i]."', '".$serial_numbers[$i]."', 0); ";
}

foreach($categories as $category) {
    $query .= "INSERT INTO table3 VALUES($question_id, $category); ";
}

$query .= "COMMIT; ";
$result = $db->query($query);

You need to add some error handling that tells you what's wrong if it doesn't work. That's how it's usually done. Seeing as we don't know what all those variables contain, it's impossible to give a meaningful answer at the moment — Pekka, Aug 17 '11 at 15:35
What do you get if you do `echo $query`? What's the MySQL error message? — JJJ, Aug 17 '11 at 15:36
Related: [How to squeeze error message out of PDO?](http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo) (assuming you're using PDO) — Pekka, Aug 17 '11 at 15:37
You cannot run multiple queries at once (separated by `;`). Try running each query separately. And if you're using PDO, have a look at [PDO::beginTransaction](http://php.net/manual/de/pdo.begintransaction.php) and [PDO::commit](http://www.php.net/manual/de/pdo.commit.php). — mAu, Aug 17 '11 at 15:37

score 1 · Accepted Answer · answered Aug 17 '11 at 15:38

1

Looks like you are attempting to run multiple statements, possibly through a mysql_query() or mysqli->query() which only support single statements. Instead you need to execute this with mysqli->multi_query() or mysql_multi_query().

answered Aug 17 '11 at 15:38

Michael Berkowski

267,341
46
444
390

score 0 · Answer 2 · answered Aug 17 '11 at 15:38

0

You are trying to run multiple queries at once, you should either run those queries one at a time or use the mysqli::multi_query method.

answered Aug 17 '11 at 15:38

nobody

10,599
4
26
43

score 0 · Answer 3 · answered Aug 17 '11 at 15:38

You're trying to run multiple queries by creating a string that contains them and then you send it to the DB. It looks like your database object doesn't support that, and the only way you can run multiple queries is by using mysqli's multi_query function.

However, there's a drawback - what if the transaction doesn't commit? What if there's an error? Where's the rollback and the error message?

PDO would be my choice here, with try/catch block and I'd create a prepared statement that I'd send rather than a huge string containing all the queries. Not only is it more elegant, it's easier to debug too.

score 0 · Answer 4 · answered Aug 17 '11 at 15:40

0

mysql_query doesn't support multiple queries

check this php manual comment for more info. In short. you should call $db->query instead of appending to $query.

note. transactions works only with innodb (at least last time I checked)

answered Aug 17 '11 at 15:40

roselan

3,755
1
20
20

Returns false when running this query

4 Answers4

Linked