按用户跟踪/验证/更新 MySQL 结果 - Track/Verify/Update a MySQL result by user

Track/Verify/Update a MySQL result by user

我有一个时间表数据库，志愿者可以在其中检查他们的班次。我将通过电子邮件向他们发送一个指向页面的链接，他们将电子邮件地址输入到 HTML 表单中以访问此信息。

有没有办法跟踪查询了哪些电子邮件，以便我可以将计划链接重新发送给尚未访问数据库的人？

如有必要，我可以在结果中添加一个额外的"已确认"复选框，并让该复选框更新数据库。我喜欢这个想法，但我不确定如何实现（或该操作的术语）。

编辑：这是我用来实现的代码。但是，我在确认列中没有得到结果。

                $db = new mysqli("host", "user", "pass", "db");
    $stmt = $db->prepare('UPDATE volConfirm SET confirmed = TRUE WHERE email = ?');
    $stmt->bind_param('s', $_POST['email']);
    $stmt->execute();
    $stmt = $db->prepare('SELECT * from volConfirm WHERE email = ?');
    $stmt->bind_param('s', $_POST['email']);
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
    // construct your output here using $row to access database record
    echo "<h2>" . $row['agreeName'] . "</h2>";
    echo "<p> You have been assigned as a volunteer for:" . $row['position'] . "</p>";
    echo "<p>Your shift times are scheduled for:" . $row['shift_times'] . "</p>";
    echo "<p>Your shift has been confirmed:" . $row['confirmed'] . "</p>";
    }

你需要做一些事情，如下：

向志愿者表添加新列

ALTER TABLE Volunteers ADD COLUMN Confirmed BOOLEAN NOT NULL DEFAULT FALSE;

让提交页面中的 PHP 更新该列：

UPDATE Volunteers SET Confirmed = TRUE WHERE Email = 'foo@bar.com';

在代码段中：

$db = new mysqli("dbhostname", "username", "password", "dbschema");
$stmt = $db->prepare('UPDATE volConfirm SET confirmed = TRUE WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();
$stmt = $db->prepare('SELECT * from volConfirm WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
  // construct your output here using $row to access database record
}

在将来的某个时候，获取尚未访问该页面的所有用户的列表：
```
SELECT Email FROM Volunteers WHERE Confirmed = FALSE;
```