我正在关注有关如何为网站创建用户注册系统的教程。我已经完成了这一步，但是我需要检查两个表中的多个值以查看它们是否存在。本教程不执行此操作。
这是我的注册表：
<form action="/members/register/" name="registerForm">
<div>
<h2>Register</h2>
<h6>welcome</h6>
<div class="register">
<input type="text" name="firstname" placeholder="First Name" required>
<input type="text" name="lastname" placeholder="Last Name" required>
<input type="text" name="email" placeholder="Email" required>
<label for="dob">Date of Birth:
<input type="date" name="dob" id="dob" placeholder="Date of Birth" required>
</label>
<input type="text" name="username" placeholder="Username" required>
<input type="password" name="password" placeholder="Password" required>
<input type="password" name="passwordconfirm" placeholder="Confirm Password" required>
<p>By creating an account you agree to our <a href="/legal/terms-of-use/">Terms & Privacy</a>.</p>
<button type="submit" name="registerNow">Register</button>
</div>
</div>
</form>
我需要检查的mySQL数据库表是：
users
-id
-username
-password
-userID (foreign key)
registered
-id
-nameFirst
-nameLast
-email
-dob
我需要创建一个查询来检查是否存在以下任何一项：1）姓和名在一起，2）用户名，或3）电子邮件。
此外，一旦我了解了如何执行这样的查询，我对查询中的内容还是有些困惑?。另外，此代码示例仅检查用户名是否存在，并输出“用户名已存在”。请选择另一个。我需要根据表中已经存在的字段输出不同的内容。这是教程中的代码：
if ($stmt = $con->prepare('SELECT id, password FROM accounts WHERE username = ?')) {
// Bind parameters (s = string, i = int, b = blob, etc), hash the password using the PHP password_hash function.
$stmt->bind_param('s', $_POST['username']);
$stmt->execute();
$stmt->store_result();
// Store the result so we can check if the account exists in the database.
if ($stmt->num_rows > 0) {
// Username already exists
echo 'Username already exists. Please choose another.';
} else {
// Insert new account
}
$stmt->close();
} else {
// Something is wrong with the sql statement, check to make sure accounts table exists with all 3 fields.
echo 'Could not prepare statement!';
}
$con->close();
会是这样吗？
SELECT id, password
FROM users, registered
WHERE users.username = ? OR (registered.nameFirst = ? AND registered.nameLast = ?) OR registered.email = ?
再说一次，我正在学习如何使用本教程来做到这一点，所以我不想就代码的操作方式对代码进行任何更改。我了解有更好的方法可以做到这一点。我以此为起点来学习和进步。
由于您要做的只是检查注册值是否已经存在，因此使用EXISTS子查询而不是将JOIN两个表放在一起可能是最简单的。像这样：
SELECT
EXISTS (SELECT * FROM users WHERE username = ?) AS found_username,
EXISTS (SELECT * FROM registered WHERE nameFirst = ? AND nameLast = ?) AS found_name,
EXISTS (SELECT * FROM registered WHERE email = ?) AS found_email
在此查询中，?代表用户名，名字，姓氏和电子邮件值的占位符。使用带占位符的预准备语句的目的是防止SQL注入，有关更多信息，请参见此Q＆A。使用它们还具有消除输入中转义特殊字符的好处（例如，如果要O'Hara使用单引号括起来的值插入nameLast字段）。
因此，对于您的代码，您将执行以下操作：
if ($stmt = $con->prepare('SELECT
EXISTS (SELECT * FROM users WHERE username = ?) AS found_username,
EXISTS (SELECT * FROM registered WHERE nameFirst = ? AND nameLast = ?) AS found_name,
EXISTS (SELECT * FROM registered WHERE email = ?) AS found_email')) {
// Bind parameters (s = string, i = int, b = blob, etc)
$stmt->bind_param('ssss', $_POST['username'], $_POST['firstname'], $_POST['lastname'], $_POST['email']);
$stmt->execute();
$stmt->bind_result($found_username, $found_name, $found_email);
$stmt->fetch();
// Store the result so we can check if the account exists in the database.
if ($found_username) {
// Username already exists
echo 'Username already exists. Please choose another.';
}
elseif ($found_name) {
// Name already exists
echo 'Name already exists. Please choose another.';
}
elseif ($found_email) {
// Email already exists
echo 'Email already exists. Please choose another.';
}
else {
// Insert new account
}
$stmt->close();
}
else {
// Something is wrong with the sql statement, check to make sure accounts table exists with all 3 fields.
echo 'Could not prepare statement!';
}
$con->close();
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句