mysqli查询向Users表重复插入数据问题求助

嘿，我一眼就瞅到你代码里导致Users表重复插入的核心问题了，咱们一步步来解决：

1. 直接元凶：重复执行了两次INSERT语句 #

看你插入用户的这段代码：

$query = "INSERT INTO Users (id, user_type, name, lastname, email, phone, school, address, state, zip, password, status) VALUES ('$userId','teacher','$name', '$lastname', '$email', '$phone', '$schoolId', '$address', '$stateName', '$zip', '$hash', 'active')";
// 第一次执行插入操作
$result = mysqli_query($con, $query);
// 第二次又执行了一遍相同的插入！
if(!$result = $con->query($query)){ 
    die('there was an error running query [' . $con->error . ']');
}else { 
    header("location: thankyou"); 
}

你先用mysqli_query跑了一次INSERT，紧接着又在if判断里用$con->query再执行了一次，这就导致每次提交表单，Users表都会被插入两条一模一样的数据。

修复方法：只执行一次插入 #

把这段代码改成这样，保证只执行一次插入：

$query = "INSERT INTO Users (id, user_type, name, lastname, email, phone, school, address, state, zip, password, status) VALUES ('$userId','teacher','$name', '$lastname', '$email', '$phone', '$schoolId', '$address', '$stateName', '$zip', '$hash', 'active')";
// 只执行一次插入
$result = $con->query($query);
if(!$result){ 
    die('there was an error running query [' . $con->error . ']');
}else { 
    header("location: thankyou"); 
    exit; // 一定要加exit，防止跳转后代码继续执行引发意外
}

2. 顺便修复userId生成的无效逻辑 #

你这段生成userId的代码完全没起到作用：

$userId = rand(1,9999999);
$check_userId ="select count(*) count from Users where user_id = " . $userId;
while ($row['count'] > 0);

这里$row根本没被赋值，循环体也是空的，要么会无限卡死，要么完全检查不了userId是否重复。改成这样才对：

// 循环生成唯一的userId
do {
    $userId = rand(1, 9999999);
    $check_userId = "SELECT COUNT(*) AS count FROM Users WHERE user_id = '$userId'";
    $result = $con->query($check_userId);
    $row = $result->fetch_assoc();
} while ($row['count'] > 0);

不过更省心的办法是把Users表的id字段设为自增主键，这样数据库会自动帮你生成唯一ID，不用手动折腾rand和查询了。

3. 额外优化建议（让代码更安全高效） #

• 用预处理语句防SQL注入：虽然你用了mysqli_real_escape_string，但预处理语句是更安全的方案，比如插入用户可以改成：

$stmt = $con->prepare("INSERT INTO Users (id, user_type, name, lastname, email, phone, school, address, state, zip, password, status) VALUES (?, 'teacher', ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active')");
$stmt->bind_param("isssssssss", $userId, $name, $lastname, $email, $phone, $schoolId, $address, $stateName, $zip, $hash);
$stmt->execute();
if($stmt->affected_rows > 0){
    header("location: thankyou");
    exit;
}else{
    die('插入失败：' . $stmt->error);
}

• 简化学校ID获取逻辑：不用先查count再查ID，也不用插入后再查一次，直接用insert_id获取刚插入的自增ID：

$checkSchool = "SELECT id from `schools` WHERE school= '$school'";
$schoolRes = mysqli_query($con, $checkSchool);
if($schoolRow = mysqli_fetch_array($schoolRes)){
    $schoolId = $schoolRow['id'];
}else{
    // 插入新学校
    $schoolquery = "INSERT INTO schools (state_id, school) VALUES ('$state','$school')";
    mysqli_query($con, $schoolquery);
    // 直接获取刚插入的学校ID，省得再查一次
    $schoolId = $con->insert_id;
}

内容的提问来源于stack exchange，提问作者T.C