returning mySQL errors from php - nothing being returned on error

Question

I have a php file which performs a series of insert queries. If any of the queries generates an error, I would like to return the error message and query string and roll back all the queries

So far I have this:

mysql_query("SET autocommit=0;");
mysql_query("BEGIN;");
$sql ="SOME MALFORMED QUERY";

mysql_query($sql);

if(mysql_error()){
    mysql_query("rollback;");
    $arr = array("returnCode" => 0, "returnMessage" => "Query failed: " .$sql. mysql_error());
    echo json_encode($arr);
    die();
}

However, In javascript all I'm seeing returned in the return message JSON field is 'Query failed: '. Any idea why this is?

Jocelyn · Accepted Answer · 2012-08-01 21:14:09Z

1

The problem is with your ROLLBACK query: as explained in the manual, mysql_error returns the error text from the last MySQL function. Since you used mysql_query again, the previous error is lost.

I suggest this code:

mysql_query("SET autocommit=0;");
mysql_query("BEGIN;");
$sql ="SOME MALFORMED QUERY";

mysql_query($sql);
$error = mysql_error();
if($error){
    mysql_query("rollback;");
    $arr = array("returnCode" => 0, "returnMessage" => "Query failed: $sql, Error: $error");
    echo json_encode($arr);
    die();
}

Executing ROLLBACK in case an error occurs seems useless. The manual explains: Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs)

The following code should then be enough:

mysql_query("SET autocommit=0;");
mysql_query("BEGIN;");
$sql ="SOME MALFORMED QUERY";

mysql_query($sql);
if(mysql_error()){
    $arr = array("returnCode" => 0, "returnMessage" => "Query failed: $sql, Error: ".mysql_error());
    echo json_encode($arr);
    die();
}

edited Aug 1, 2012 at 21:14

answered Aug 1, 2012 at 12:33

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roger Jarvis Over a year ago

Thanks, this makes sense. Although, I wonder why the $sql variable is blank as well?

Jocelyn Over a year ago

Maybe this wasn't entirely clear: I had posted only the modified part of your code. I just updated my answer by including the full modified version.

AmazingDreams · Accepted Answer · 2012-08-01 11:59:25Z

0

Shouldn't

$arr = array("returnCode" => 0, "returnMessage" => "Query failed: " .$sql. sql_error());

be

$arr = array("returnCode" => 0, "returnMessage" => "Query failed: " .$sql. **mysql_error()**);

edited Aug 1, 2012 at 11:59

user657496

answered Aug 1, 2012 at 11:58

AmazingDreams

3,2342 gold badges24 silver badges32 bronze badges

1 Comment

Roger Jarvis Over a year ago

sorry! it should be that, but I made an error when copying the code over. I've edited the question

cybertextron · Accepted Answer · 2012-08-01 12:16:33Z

0

Probably the expression "Query failed: " .$sql. sql_error() is not well formed. The correct form is:

"Query failed: " . $sql . mysql_error()

edited Aug 1, 2012 at 12:16

answered Aug 1, 2012 at 11:59

cybertextron

11k32 gold badges117 silver badges220 bronze badges

Comments

Martin Bean · Accepted Answer · 2012-08-01 12:46:15Z

0

I'd use PDO and its built-in transactions support.

<?php

// create PDO instance as $db here
// don't forget to set error mode to 'exception'

try {
    $db->beginTransaction();
    // do your queries here, i.e. $db->exec('BOGUS');
    $db->commit();
}
catch (Exception $e) {
    $db->rollBack();
    echo 'Error: ' . $e->getMessage();
}

It will give you any error encountered as the exception message when it throws one.

answered Aug 1, 2012 at 12:46

Martin Bean

39.6k28 gold badges134 silver badges207 bronze badges

Collectives™ on Stack Overflow

returning mySQL errors from php - nothing being returned on error

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related