PHP+MySQL用户系统问题：更新/用户类型/图片上传/删除求助

Hey there! Let's break down all your issues step by step and fix them up properly.

一、MySQL & 程序中设置用户类型 #

1. 数据库层面修改 #

首先需要给user表添加一个user_type字段来区分用户角色（比如普通用户/管理员），执行以下SQL语句：

ALTER TABLE user ADD COLUMN user_type ENUM('user', 'admin') DEFAULT 'user';

如果需要更多角色类型，也可以用VARCHAR或者单独建一个角色关联表，ENUM适合固定角色的场景。

2. 程序层面整合 #

• 在注册页面添加用户类型选择项（默认普通用户）
• 在编辑页面允许修改用户类型（可根据登录用户权限控制是否显示）

二、修复程序报错问题 #

你的代码存在几个核心问题导致报错：SQL注入风险、未正确获取URL参数、HTML语法错误。下面是修正后的关键文件：

1. Register.php（修复注入、添加用户类型+图片上传支持） #

<?php 
session_start(); 
require('connect.php'); 

if(isset($_POST['submit'])){
    // 接收表单数据
    $username = $_POST['username'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    $password = password_hash($_POST['password'], PASSWORD_DEFAULT); // 替换md5，更安全
    $dob = $_POST['dob'];
    $gender = $_POST['gender'];
    $location = $_POST['location'];
    $course = $_POST['course'];
    $mobile = $_POST['mobile'];
    $user_type = $_POST['user_type'] ?? 'user';
    $profile_image = null;

    // 处理图片上传
    if(isset($_FILES['profile_image']) && $_FILES['profile_image']['error'] == UPLOAD_ERR_OK){
        $upload_dir = 'uploads/';
        if(!file_exists($upload_dir)) mkdir($upload_dir, 0755, true);
        $filename = uniqid() . '_' . basename($_FILES['profile_image']['name']);
        $target_file = $upload_dir . $filename;
        $image_type = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));
        
        if(in_array($image_type, ['jpg', 'jpeg', 'png', 'gif'])){
            if(move_uploaded_file($_FILES['profile_image']['tmp_name'], $target_file)){
                $profile_image = $filename;
            }else{
                $fmsg = "Failed to upload image";
            }
        }else{
            $fmsg = "Only JPG, JPEG, PNG & GIF files are allowed";
        }
    }

    // 预处理SQL，防止注入
    $query = "INSERT INTO `user`(username, name, password, email, dob, gender, location, course, mobile, user_type, profile_image) 
              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    $stmt = mysqli_prepare($mysqli, $query);
    mysqli_stmt_bind_param($stmt, "sssssssssss", $username, $name, $password, $email, $dob, $gender, $location, $course, $mobile, $user_type, $profile_image);
    
    if(mysqli_stmt_execute($stmt)){
        $smsg = "Thank you! Your registration has been submitted successfully.";
    }else{
        $fmsg = "Error: " . mysqli_error($mysqli); // 显示真实错误，方便调试
    }
    mysqli_stmt_close($stmt);
} 
?>
<html>
<head>
<title>User Registration</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<link rel="stylesheet" href="styles.css">
</head>
<body>
<div class="container mt-5">
<?php if(isset($smsg)){ ?><div class="alert alert-success" role="alert"><?php echo $smsg; ?></div><?php } ?>
<?php if(isset($fmsg)){ ?><div class="alert alert-danger" role="alert"><?php echo $fmsg; ?></div><?php } ?>

<form class="form-signin" method="POST" enctype="multipart/form-data">
<h2 class="form-signin-heading">Please Register</h2>
<div class="input-group mb-2">
<span class="input-group-addon" id="basic-addon1">ID</span>
<input type="text" name="username" class="form-control" placeholder="Username" required>
</div>
<input type="text" name="name" class="form-control mb-2" placeholder="Full Name" required>
<input type="email" name="email" class="form-control mb-2" placeholder="Email Address" required>
<input type="password" name="password" class="form-control mb-2" placeholder="Password" required>
<input type="date" name="dob" class="form-control mb-2" placeholder="DOB" required>

<div class="mb-2">
Gender:
<input type="radio" name="gender" value="m" required> Male
<input type="radio" name="gender" value="f"> Female
</div>

<input type="text" name="location" class="form-control mb-2" placeholder="Location" required>
<input type="text" name="course" class="form-control mb-2" placeholder="Course" required>
<input type="text" name="mobile" class="form-control mb-2" placeholder="Mobile" required>

<div class="mb-2">
<label>User Type:</label>
<select name="user_type" class="form-control">
    <option value="user">Regular User</option>
    <option value="admin">Admin</option>
</select>
</div>

<div class="mb-2">
<label>Profile Image:</label>
<input type="file" name="profile_image" class="form-control" accept="image/*">
</div>

<button class="btn btn-lg btn-primary btn-block" type="submit" name="submit">Register</button>
<a class="btn btn-lg btn-secondary btn-block mt-2" href="login.php">Login</a>
</form>
</div>
</body>
</html>

2. Edit.php（修复ID获取、注入问题，填充原有数据） #

<?php 
session_start(); 
require('connect.php'); 

// 获取URL中的用户ID
$id = $_GET['id'] ?? $_POST['id'] ?? '';
if(empty($id)){
    header('location: login.php');
    exit;
}

// 获取当前用户信息
$user = mysqli_fetch_assoc(mysqli_query($mysqli, "SELECT * FROM user WHERE id = '$id'"));

if(isset($_POST['submit'])){
    $name = $_POST['name'];
    $dob = $_POST['dob'];
    $gender = $_POST['gender'];
    $location = $_POST['location'];
    $course = $_POST['course'];
    $mobile = $_POST['mobile'];
    $user_type = $_POST['user_type'];

    // 预处理更新语句
    $sql = "UPDATE user SET name = ?, dob = ?, gender = ?, location = ?, course = ?, mobile = ?, user_type = ? WHERE id= ?";
    $stmt = mysqli_prepare($mysqli, $sql);
    mysqli_stmt_bind_param($stmt, "sssssssi", $name, $dob, $gender, $location, $course, $mobile, $user_type, $id);
    
    if (mysqli_stmt_execute($stmt)) {
        $smsg = "Record updated successfully";
        $user = mysqli_fetch_assoc(mysqli_query($mysqli, "SELECT * FROM user WHERE id = '$id'"));
    } else {
        $fmsg = "Error updating record: " . mysqli_error($mysqli);
    }
    mysqli_stmt_close($stmt);
} 
?>
<html>
<head>
<title>Edit Profile</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<link rel="stylesheet" href="main.css">
</head>
<body>
<div class="container mt-5">
<a class="float-right" href="logout.php">LOGOUT</a>
<?php if(isset($smsg)){ ?><div class="alert alert-success" role="alert"><?php echo $smsg; ?></div><?php } ?>
<?php if(isset($fmsg)){ ?><div class="alert alert-danger" role="alert"><?php echo $fmsg; ?></div><?php } ?>

<form class="form-signin" method="POST">
<h2 class="form-signin-heading">Edit Profile</h2>
<input type="hidden" name="id" value="<?php echo $user['id'];?>">

<input type="text" name="name" class="form-control mb-2" placeholder="Full Name" required value="<?php echo $user['name'];?>">
<input type="date" name="dob" class="form-control mb-2" placeholder="DOB" required value="<?php echo $user['dob'];?>">

<div class="mb-2">
Gender:
<input type="radio" name="gender" value="m" <?php echo $user['gender'] == 'm' ? 'checked' : '';?> required> Male
<input type="radio" name="gender" value="f" <?php echo $user['gender'] == 'f' ? 'checked' : '';?>> Female
</div>

<input type="text" name="location" class="form-control mb-2" placeholder="Location" required value="<?php echo $user['location'];?>">
<input type="text" name="course" class="form-control mb-2" placeholder="Course" required value="<?php echo $user['course'];?>">
<input type="text" name="mobile" class="form-control mb-2" placeholder="Mobile" required value="<?php echo $user['mobile'];?>">

<div class="mb-2">
<label>User Type:</label>
<select name="user_type" class="form-control">
    <option value="user" <?php echo $user['user_type'] == 'user' ? 'selected' : '';?>>Regular User</option>
    <option value="admin" <?php echo $user['user_type'] == 'admin' ? 'selected' : '';?>>Admin</option>
</select>
</div>

<button class="btn btn-lg btn-primary btn-block mt-2" type="submit" name="submit">Update</button>
</form>
</div>
</body>
</html>

3. Dashboard.php（原Login.php改为用户列表，修复登录逻辑） #

<?php 
session_start(); 
require("connect.php"); 

// 检查登录状态
if(!isset($_SESSION['username'])){
    header('location: login.php');
    exit;
}

// 处理删除请求
if(isset($_GET['delete_id'])){
    $delete_id = $_GET['delete_id'];
    $stmt = mysqli_prepare($mysqli, "DELETE FROM user WHERE id = ?");
    mysqli_stmt_bind_param($stmt, "i", $delete_id);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    header('location: dashboard.php');
    exit;
}
?>
<html>
<head>
<title>User Dashboard</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-5">
<b><a class="float-right" href="logout.php">LOGOUT</a></b>
<h1>Welcome, <?php echo $_SESSION['username']; ?>!</h1>

<table class="table table-striped mt-3">
<thead>
<tr>
<th>Username</th>
<th>Full Name</th>
<th>Email</th>
<th>DOB</th>
<th>Gender</th>
<th>Location</th>
<th>Course</th>
<th>Mobile</th>
<th>User Type</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php 
$query = "SELECT * FROM user";
if ($result = $mysqli->query($query)) {
    while ($row = $result->fetch_assoc()) {
        echo '<tr>
        <td>'.$row["username"].'</td>
        <td>'.$row["name"].'</td>
        <td>'.$row["email"].'</td>
        <td>'.$row["dob"].'</td>
        <td>'.$row["gender"].'</td>
        <td>'.$row["location"].'</td>
        <td>'.$row["course"].'</td>
        <td>'.$row["mobile"].'</td>
        <td>'.$row["user_type"].'</td>
        <td>
            <a href="edit.php?id=' . $row['id'] . '" class="btn btn-sm btn-primary">Edit</a>
            <a href="dashboard.php?delete_id=' . $row['id'] . '" class="btn btn-sm btn-danger" onclick="return confirm(\'Are you sure?\')">Delete</a>
        </td>
        </tr>';
    }
    $result->free(); 
} 
?>
</tbody>
</table>
</div>
</body>
</html>

三、实现图片上传的额外步骤 #

1. 添加数据库字段：

ALTER TABLE user ADD COLUMN profile_image VARCHAR(255) DEFAULT NULL;

2. 创建上传目录：在项目根目录新建uploads文件夹，并设置权限为755（确保PHP能写入）。

四、删除存储的数据 #

上面的Dashboard.php已经实现了删除功能：

• 前端点击删除按钮会触发确认提示，防止误删
• 后端通过预处理语句执行删除操作，避免注入风险

关键注意事项 #

• 密码安全：永远不要用md5存储密码，改用password_hash和password_verify，符合现代安全标准。
• SQL注入：所有数据库操作必须用预处理语句，禁止直接拼接用户输入到SQL中。
• 错误调试：开发阶段可以开启PHP错误提示，或者用mysqli_error()输出具体错误，快速定位问题。

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