php - 查找具有匹配值并更新字段的 MySQL 表

Question

我创建了一个注册验证系统，用户会收到一封电子邮件，其中包含用于验证其帐户的链接。单击链接后，他们将进入以下 PHP 页面:

<?php
$passkey = trim(mysqli_real_escape_string($connection, $_GET['passkey']));

$query = "UPDATE pims SET com_code=NULL WHERE com_code='$passkey' UNION ALL UPDATE dms SET com_code=NULL WHERE com_code='$passkey' UNION ALL UPDATE users SET com_code='$passkey'";

$result = mysqli_query($connection, $query);
    if (!result) {
        die("Database query failed: " . mysqli_error($result));
    }

if (mysqli_num_rows($result) == 0) {
    echo '<div>Sorry. Something went wrong! Please contact the site admin at <a href="mailto:support@domain.com">support@domain.com</a> for assistance.</div>';

} else {
    echo '<div>Your account is now active. You may now <a href="login.php">Log in</a></div>';
}
?>

我想将 com_code 设置为 NULL，但首先我需要通过匹配 $passkey 变量在 3 个表(pims、dms 和 users)之一中找到用户。

这不起作用。有什么建议吗？

<小时/>

更新:

我按照 Sean 指出的那样修复了代码:

$query = "UPDATE pims SET com_code=NULL WHERE com_code='$passkey' 
UNION ALL UPDATE dms SET com_code=NULL WHERE com_code='$passkey' 
UNION ALL UPDATE users SET com_code=NULL WHERE com_code='$passkey'";

但是，它仍然不起作用。

<小时/>

更新:

我再次更新了代码，如下所示:

<?php
    $passkey = mysqli_real_escape_string($connection, $_GET['passkey']);

    $query = "UPDATE pims, dms, users SET pims.com_code=NULL, dms.com_code=NULL, users.com_code=NULL WHERE pims.com_code='$passkey' AND dms.com_code='$passkey' AND users.com_code='$passkey'";

    $result = mysqli_query($connection, $query);
        if (mysqli_affected_rows ($connection) > 0) {
            echo '<div>Your account is now active. You may now <a href="login.php">Log in</a></div>';
        } else {
            echo '<div>ERROR MESSAGE<br><br>Sorry, something went wrong!<br><br>Please contact the site admin at <a href="mailto:support@domain.com">support@domain.com</a> for assistance.</div>';
    }

    mysqli_close($connection);
?>

但由于某种原因它无法连接。

<小时/>

更新:

所以，我决定进行 3 个这样的查询:

<?php
    $passkey = mysqli_real_escape_string($connection, $_GET['passkey']);

    $query = "UPDATE pims SET com_code=NULL WHERE com_code='{$passkey}'";

    $result = mysqli_query($connection, $query);
        if (mysqli_affected_rows ($connection) <= 0) {

            $query = "UPDATE dms SET com_code=NULL WHERE com_code='{$passkey}'";

            $result = mysqli_query($connection, $query);
                if (mysqli_affected_rows ($connection) <= 0) {

                    $query = "UPDATE users SET com_code=NULL WHERE com_code='{$passkey}'";

                    $result = mysqli_query($connection, $query);
                        if (mysqli_affected_rows ($connection) <= 0) {
                            echo '<div> ERROR MESSAGE<br><br>Sorry, something went wrong!<br><br>Please contact the site admin at <a href="mailto:support@domain.com">support@domain.com</a> for assistance.</div>';
                        } else {
                            echo '<div>Your account is now active. You may now <a href="login.php">Log in</a></div>';
                        }
                } else {
                    echo '<div>Your account is now active. You may now <a href="login.php">Log in</a></div>';
                }
        } else {
            echo '<div>Your account is now active. You may now <a href="login.php">Log in</a></div>';
        }

    mysqli_close($connection);
?>

它可以工作，但看起来很慢。任何改进建议将不胜感激。

Answer 1

尝试在 1 个查询中完成所有操作，而不是执行 UNION ALL

UPDATE 
 pims,dms,users
SET
 pims.com_code=NULL, dms.com_code=NULL, users.com_code=NULL
WHERE 
 pims.com_code='$passkey'
AND
 dms.com_code='$passkey'
AND
 users.com_code='$passkey'

SQL Fiddle 示例 - http://sqlfiddle.com/#!2/d0464d/1