php - 如何在更新前检查另一个表？

Question

我有这两张表:

 // users
+----+-------+-----------------------+--------+
| id | name  |       email           | active |
+----+-------+-----------------------+--------+
| 1  | peter | peter12@hotmail.com   | NULL   |
| 2  | jack  | most_wanted@gmail.com | NULL   |
| 3  | john  | john_20016@yahoo.com  | NULL   |
+----+-------+-----------------------+--------+

// activate
+----+---------+---------------------+
| id | post_id |   random_string     |
+----+---------+---------------------+
| 1  | 2       | fewklw23523kf       |
+----+---------+---------------------+

我也有这样的 URL:

http://example.com/activate.php?str=fewklw23523kf

---

我想做的就是:

• 将 GET['str'] 与 activate 表中的 random_string 列进行比较
• 检查 active 列中的 NULL，其中 id = post_id。

然后(如果有匹配的行)将 users 表中的 active 列设置为 1。我该怎么做？

---

$str = $_GET['str'];

$stm = $db_con->prepare( "UPDATE users SET active = 1
                          WHERE ( SELECT 1 FROM activate WHERE random_string = ? ) t AND
                                ( SELECT 1 FROM users WHERE t.post_id = id AND
                                                            active IS NULL ) ");

$stmt->execute(array($str));

我的查询没有按预期工作。

Answer 1

你可以使用连接

UPDATE users 
INNER JOIN activate on activate.post_id = user.id
SET active = 1        
WHERE activate.random_string = ? 
AND user.active IS NULL;