PHP MySQL注册功能未校验邮箱值问题求助

Hey there, let's sort out that email validation issue you're dealing with! The root problem in your code is that you're only fetching the first row of data from the users table with mysqli_fetch_assoc($myque). That means your check only works if the duplicate email happens to be in that first row—all other existing emails in the table get completely ignored.

Here's how to fix it properly: #

Instead of pulling the entire users table and checking just one row, we can run targeted database queries to directly check if the submitted username or email already exists. This is also way more efficient than loading every user record.

Modified Code:

<?php include 'template-parts/header.php'; ?>
<main class="main-wrap">
<div class="container">
<main class="main-content">
<div class="login-form-wrap">
<div class="phdr">Registration</div>
<div class="pad">
<?php 
include 'config/connection.php';
$error = '';

if (isset($_POST['regbtn'])) {
    $name = trim($_POST['regname']);
    $email = trim($_POST['regemail']);
    $password = $_POST['regpassword'];
    $phone = $_POST['regphone'];

    // Check if username exists
    $check_username = "SELECT username FROM users WHERE username = ?";
    $stmt = mysqli_prepare($con, $check_username);
    mysqli_stmt_bind_param($stmt, "s", $name);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_store_result($stmt);
    
    if (mysqli_stmt_num_rows($stmt) > 0) {
        $error .= 'Username Already Exist!';
    }
    mysqli_stmt_close($stmt);

    // Check if email exists
    $check_email = "SELECT useremail FROM users WHERE useremail = ?";
    $stmt = mysqli_prepare($con, $check_email);
    mysqli_stmt_bind_param($stmt, "s", $email);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_store_result($stmt);
    
    if (mysqli_stmt_num_rows($stmt) > 0) {
        $error .= ($error ? '<br>' : '') . 'Email Already Exist!';
    }
    mysqli_stmt_close($stmt);

    // Show error alerts if any
    if (!empty($error)) {
        echo "<script type='text/javascript'>alert('$error');</script>";
    }

    // Optional: If no errors, proceed to insert new user
    // else {
    //     $insert_query = "INSERT INTO users (username, useremail, password, phone) VALUES (?, ?, ?, ?)";
    //     // ... handle insertion logic here
    // }
}
?>
<form action="" method="post">
<p>
<label for="name"></label>
<input type="text" id="name" name="regname" placeholder="Enter Full Name..." required>
</p>
<p>
<label for="email"></label>
<input type="email" id="email" name="regemail" placeholder="Enter Email Address..." required>
</p>
<p>
<label for="password"></label>
<input type="password" id="password" name="regpassword" placeholder="Enter Password..." required>
</p>
<p>
<label for="phone"></label>
<input type="text" id="phone" name="regphone" placeholder="Enter Phone Number..." required>
</p>
<p>
<input type="submit" name="regbtn" value="Sign Up">
</p>
</form>
</div>
</div>
</main>
</div>

Key Improvements: #

• Targeted Queries: We use WHERE clauses to only check for the specific username/email submitted, instead of loading all users.
• Prepared Statements: This prevents SQL injection (a critical security issue your original code was vulnerable to) and ensures safe handling of user input.
• Full Table Check: Now the code checks every row in the users table for duplicates, not just the first one.
• Combined Error Messages: If both username and email are duplicates, you'll get a single alert with both messages.
• Input Sanitization: Added trim() to remove extra whitespace from username/email, and set required attributes on inputs to enforce basic client-side validation.

Why Your Original Code Failed: #

When you ran mysqli_fetch_assoc($myque), you only retrieved the first record from the users table. So if the duplicate email was in any row after the first, your code never checked it against the submitted email. By using mysqli_stmt_num_rows() on targeted queries, we can directly confirm if a matching record exists anywhere in the table.

内容的提问来源于stack exchange，提问作者user9885547