php - SQL - 事务更新 2 个表

Question

我有一个表单要求用户验证他们的电子邮件地址，链接发送给用户

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

用户被定向到激活页面。

我正在尝试从 url 获取 email、key 和 api。然后我尝试更新用户表和花名册表。

名册表列更新是激活要更新的用户表列是 groups它们都将 MD5 随机散列的唯一值作为 API KEY 在整个网站上传递。一切都运行顺利，只是不是查询。

有什么地方出错了吗？

    <?php
include ('dbcon.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))
     //The Activation key will always be 32 since it is MD5 Hash
     {
     $key = $_GET['key'];
    }
    if (isset($_GET['api']) && (strlen($_GET['api']) == 32))
     //The API key will always be 32 since it is MD5 Hash
     {
     $API = $_GET['api'];
    }
    if (isset($email) && isset($key)) {

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

     $query_activate_account = "BEGIN TRANSACTION; 
                                UPDATE table_roster SET Activation=NULL WHERE(email ='$email' AND Activation='$key')LIMIT 1;
                                UPDATE table_users SET groups=[99] WHERE(pinAPP_API ='$API') LIMIT 1;
                                COMMIT";
     $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>';
    }
    ?>

我在 SO 上搜索了一些关于用一个事务更新两个表的信息，建议使用 BEGIN TRANSACTION; UPDATE... UPDATE... COMMIT; 然而我遇到了一个失败，我预定义的错误消息是 An Error Occurred

Answer 1

您必须使用 mysqli_multi_query而不是 mysqli_query。

您还必须启动事务并使用单独的查询提交或回滚它。

mysqli_query($dbc, "START TRANSACTION");

$result_activate_account = mysqli_multi_query(
    $dbc,
    "UPDATE table_roster SET Activation=NULL WHERE (email ='$email' AND Activation='$key') LIMIT 1;
     UPDATE table_users SET groups=[99] WHERE (pinAPP_API ='$API') 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>';
}