PHP MYSQL transaction returns error SQLSTATE[HY000]: General error

Question

I got this code:

$qry = "BEGIN; SELECT title FROM properties WHERE id=? LIMIT 1; COMMIT";
$arr = array(59);
$stmt = $data_users->default_query($qry, $arr);

print_r($stmt);

and this is my function:

public function default_query($qry, $arr)
{
    try {
        $stmt = $this->con->prepare($qry);
        $stmt->execute($arr);

        return $stmt->fetch(PDO::FETCH_ASSOC);
    } catch(PDOException $e) {
         echo  $e->getMessage();
    }
}

and it returns the error when I try to process the result, however, if I do the same query directly on SQL I got this result:

title

title test

This is the desire result, but I can't get it on my App. However if I change the $qry to:

//Remove the begin And Commit stuff;
$qry = "SELECT title FROM properties WHERE id=? LIMIT 1";

It works, the problem is that I need to use transactions because I need to do some stuff later, insert last id on some other tables and on, I reduced the code to the minimum to reproduce the error, but I can't figured it out, I think it has something to do with the function's return but on the manual it says

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

And that's what I need at this moment. Am I using the wrong FETCH?

UPDATE

Ok, I tried this new function and now i get a Boolean result

$qry = "SELECT title FROM properties WHERE id=59 LIMIT 1";

public function commit_query($qry){
    try {
        $stmt = $this->con->beginTransaction();
    
        $stmt = $this->con->prepare($qry);
        $stmt->execute();
        $stmt = $this->con->commit();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        $stmt = $this->con->rollback();
    }
}
// I got this error which its OK according to this

Returns TRUE on success or FALSE on failure.

Fatal error: Call to a member function fetch() on boolean in

Is there anyway to get a Fetch result here?

Yet Another Update

Ok now I got this function:

public function commit_query_withId($qry,$arr,$multiqry){
try {
$stmt = $this->con->beginTransaction();

$stmt = $this->con->prepare($qry);
$stmt->execute($arr);
$lastId = $this->con->lastInsertId();
    
$stmt = $this->con->prepare($multiqry);
$stmt->execute();
    
$stmt = $this->con->commit();
    
return compact("lastId","stmt");
    
} catch (Exception $e) {
    $stmt = $this->con->rollback();
}
}

And I send these queries:

//capturedby comes from an array ex:<select name="capturedby[]"
$elements = $_POST['capturedby'];

foreach ($elements as $x){
    $sql[] = '(LAST_INSERT_ID(), '.$x.')';
}
$data_users=new database_data();

$qry = "INSERT INTO properties (title, ...)". "VALUES (:tit,...);";
$arr = array(':tit'=>$_POST['title'],...);

$multiqry='INSERT INTO captured_by (property_id, users_admin_id) VALUES '.implode(',', $sql);

$stmt=$data_users->commit_query_withId($qry,$arr,$multiqry);

if ($stmt["stmt"]==true) {
    $data['valid'] = true;
    $data['response'] = $msg_echo->messages('3');
    $data['id'] = $stmt['lastId'];
}else{
    $data['valid'] = false;
    $data['response'] = $msg_echo->messages('4');
}

Now I got 2 successful inserts and the last inserted id inside a $var, so I guess this works for now.

This might be useful for infomation http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — RiggsFolly, Aug 06 '16 at 17:08

Danila Ganchar · Accepted Answer · 2016-08-07T13:45:20.210

0

The best solution is use PDO tools for transactions. And remove BEGIN, COMMIT from sql query.

About PDO and commit()... I think you got the error because fetch() calls after commit(). This way without error:

$stmt = $this->con->beginTransaction();
$stmt = $this->con->prepare($qry);
$stmt->execute();

$result = $stmt->fetch(PDO::FETCH_ASSOC);

$this->con->commit();

return $result;

I hope this is what you need.

edited Aug 07 '16 at 13:45

answered Aug 06 '16 at 16:54

Danila Ganchar

10,266
13
49
75

Really a comment but I have to agree – RiggsFolly Aug 06 '16 at 17:01
I'm agree with you - too short answer ))) – Danila Ganchar Aug 06 '16 at 17:23
1

I Don't know why someone down voted this one, it got me on the right track, I haven't accept it as an answer just because it doesn't solve the Fetch result just yet... for now I am using the true value to keep going, I will post the code in a few – ryangus Aug 06 '16 at 23:28
Nope, it doesn't work for me, I think we cannot call fetch inside transaction... it throws this error: "!exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in ...functions.php:221 Stack trace: #0 ... functions.php(221): PDOStatement->fetch(2) #1 ...addProperty.php(33): database_data->commit_query_test('INSERT INTO pro...', Array) #2 {main}1" – ryangus Aug 07 '16 at 16:18
if I try with rowcount: $result = $stmt->rowCount(); I can get that, but the fetch method doesn't work, I'll check the docs about it. – ryangus Aug 07 '16 at 16:35
OMG, of course, I was trying to get a fetch From an insert, that's why... my bad... thanks for your help! – ryangus Aug 07 '16 at 17:27

PHP MYSQL transaction returns error SQLSTATE[HY000]: General error

1 Answers1