MySQL Transaction - update 2 tables

Question

I have a form that requires the user to confirm their email address, the link is sent to the user as

http://app.myurl.org/h/activate.php?email=useremail%40gmail.com&key=80fddb7fa21dd2e2639ae5ec82b9d511&api=8a2d01d7411ec2488307744ddf070a4d

User accesses activate.php

I'm trying to get email, keyand apifrom the URL-addresses.

Then I try to update the user table and registry table .

wrightway_roster Table column to update - Activation

wrightway_users Table column to update - groups

They both pass the unique value of the random MD5 hash. Everything runs smoothly, just not a TRANSACTION request.

<?php
include ('database_connection.php');
if (isset($_GET['email']) && preg_match('/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/', $_GET['email'])) { $email = $_GET['email']; }
if (isset($_GET['key']) && (strlen($_GET['key']) == 32)) { $key = $_GET['key'];}
if (isset($_GET['api']) && (strlen($_GET['api']) == 32)) { $API = $_GET['api'];}
if (isset($email) && isset($key)) {
    mysqli_query($dbc, "START TRANSACTION");
    $result_activate_account = mysqli_multi_query($dbc,"
       UPDATE table_users SET groups=[99] WHERE(pinAPP_API ='$API') LIMIT 1;
       UPDATE wrightway_roster SET Activation=NULL WHERE(email ='$email' AND Activation='$key')LIMIT 1;
    ");
    if ($result_activate_account !== false) {
        mysqli_query($dbc, "COMMIT");
        echo '<div>You may now proceed.</div>';
    } else {
        mysqli_query($dbc, "ROLLBACK");
        echo '<div>Oops !You could not be validated. Please recheck the link or contact your hiring manager.</div>';
    }
    mysqli_close($dbc);
} else {
    echo '<div>An Error Occurred.</div>';
}
?>

PHP has a value of 5.4 Native

both Activationand groups varchar

These queries work and do exactly what is intended.

"UPDATE wrightway_users SET groups='[99]' WHERE(pinAPP_API ='$API') LIMIT 1;";

UPDATE wrightway_roster SET Activation=NULL WHERE(email ='$email' AND Activation='$key')LIMIT 1";

I used one request or for what I need for this, I wrote above to use transactions.

if (isset($email) && isset($key)) {

     // Update the database to set the "activation" field to null

     $query_activate_account = "UPDATE wrightway_users SET groups='[99]' WHERE(pinAPP_API ='$API') LIMIT 1;";

     $result_activate_account = mysqli_query($dbc, $query_activate_account);

     // Print a customized message:
     if (mysqli_affected_rows($dbc) == 1) //if update query was successful
     {
     echo '<div>You may now proceed.</div>';

     } else {
     echo '<div>Oops !You could not be validated. Please recheck the link or contact your hiring manager.</div>';

     }

     mysqli_close($dbc);

    } else {
     echo '<div>An Error Occurred.</div>';
    }

Answer 1

. , , , !

if (isset($email) && isset($key)) {
    mysqli_query($dbc);
    $verified = mysqli_query($dbc,"
       UPDATE tbl_users t1, tbl_roster t2 SET t1.groups='[22]', t2.prescreen='0' WHERE t1.API='$API' AND t2.API='$API' AND t2.prescreen='$key';
    ");
    if ($verified !== false) {
        mysqli_query($dbc);
        echo '<br><center><div class="success"></div>';

    } 
    else {
        mysqli_query($dbc);
        echo '<br><div class="validation"></div>';
    }
    mysqli_close($dbc);
  } else {
    echo '<br><div class="error">An Error Occurred.</div>';
}

Answer 2

MySQL

UPDATE wrightway_users SET groups=[99] ...
                                  ^  ^

, :

UPDATE wrightway_users SET groups=99 ...
UPDATE wrightway_users SET groups="99"
UPDATE wrightway_users SET groups="[99]"

.

, , .

Answer 3

mysqli_begin_transaction(), mysqli_rollback() mysqli_commit() begin transaction mysqli_query()?

:

https://secure.php.net/manual/en/mysqli.begin-transaction.php

Answer 4

, - ?, , 99?

Im :

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '[33] where id=1' at line 1

You can use mysqli_error to check for the latest error.