CODEWITHSUNDEEP
phpmysqlitransactions
Mulgard
Mulgard

Reputation: 10569

Transactions not doing the rollback

I have a function to update a user:

function update_user_set_common($date_of_birth, $mobile, $street, $housenumber, $addition, $postal_code, $location, $country)
{
    try {
        $this->mysqli->begin_transaction();

        $this->update_user_set_date_of_birth($date_of_birth);
        $this->update_user_set_mobile($mobile);
        $this->update_user_set_address($street, $housenumber, $addition, $postal_code, $location, $country);

        $this->mysqli->commit();

        return true;
    } catch (Exception $e) {
        $this->mysqli->rollback();

        echo "Error: " . $e;

        return false;
    }
}

As you can see i open up a transaction. When everything is ok it should commit, if an error occurs it should rollback.

This is an example how of my update queries:

function update_user_set_date_of_birth($date_of_birth)
{
    return $this->update_user_set_singlefield(COL_USER_DATE_OF_BIRTH, $date_of_birth);
}

function update_user_set_singlefield($field, $value)
{
    if ($update_stmt = $this->mysqli->prepare("UPDATE " . TABLE_USER . " SET " . $field . " = ? WHERE " . COL_USER_ID . " = ?")) :
        if (!$update_stmt->bind_param("ss", $value, $this->USER_ID)) :
            $update_stmt->close();

            return false;
        endif;

        if (!$update_stmt->execute()) :
            $update_stmt->close();

            return false;
        else :
            $update_stmt->close();

            return true;
        endif;
    else :
        return false;
    endif;
}

So now for example the update_user_set_mobile fails but there is no rollback. the statement before update_user_set_date_of_birth is still performed and is not changed back.

Why is there no rollback?

Edit

Example create table to show that i use innodb:

CREATE TABLE `USER` (
  `USER_ID` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `USER_E_MAIL` varchar(255) COLLATE utf8_bin NOT NULL,
  `USER_PASSWORT` varchar(255) COLLATE utf8_bin NOT NULL,
  `USER_PASSWORT_SALT` varchar(255) COLLATE utf8_bin NOT NULL,
  `USER_FIRSTNAME` varchar(255) COLLATE utf8_bin NOT NULL,
  `USER_LASTNAME` varchar(255) COLLATE utf8_bin NOT NULL,
  `USER_DATE_OF_BIRTH` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `USER_MOBILE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ADDRESS_ID` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Upvotes: 0

Views: 70

Answers (1)

miralong
miralong

Reputation: 842

turn autocommit off instead of mysqli->begin_transaction();

$this->mysqli->autocommit(false);

http://php.net/manual/en/mysqli.autocommit.php

Upvotes: 1

Related Questions