user1212548 user1212548 - 3 months ago 11
MySQL Question

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

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?
Thanks in advanced.

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.

Answer

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.

Comments