PHP-MySQL用户登录系统：所有用户显示相同作物的问题修复

我正在开发基于PHP的用户登录系统，预期每个用户可查看数据库crop中的个人专属作物数据，但目前所有用户均显示相同的‘最新作物’。以下是数据库表结构及创建新作物的PHP代码，请问如何修复该问题？

数据库表结构 #

CREATE TABLE `users` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE `crop` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `qty` INT NOT NULL,
    `pot_size` INT NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `thc` INT NOT NULL,
    `yield` INT NOT NULL,
    `ready` INT NOT NULL,
    `genetics` VARCHAR(50) NOT NULL,
    `soil` VARCHAR(50) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `startdate` DATE NOT NULL,
    `enddate` DATE NOT NULL,
    `total_harvested` INT NOT NULL,
    `strain_rating` INT DEFAULT NULL);

CREATE TABLE `user_crop` (
  `user_id` INT NOT NULL,
  `crop_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `crop_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`crop_id`) REFERENCES `crop` (`id`) ON DELETE CASCADE);

CREATE TABLE `watering` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `crop_id` INT NOT NULL,
    `ph` FLOAT NOT NULL,
    `root_juice` FLOAT NOT NULL,
    `bio_grow` FLOAT NOT NULL,
    `bio_bloom` FLOAT NOT NULL,
    `top_max` FLOAT NOT NULL,
    `bio_heaven` FLOAT NOT NULL,
    `acti_vera` FLOAT NOT NULL,
    `wdate` DATETIME(6) NOT NULL,
    FOREIGN KEY (`crop_id`) REFERENCES `crop` (`id`) ON DELETE CASCADE);

CREATE TABLE `weather` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `temperature` FLOAT NOT NULL,
    `humidity` FLOAT NOT NULL,
    `date` DATETIME(6) NOT NULL);

创建新作物的PHP代码 #

<?php include 'include/dbconnect.php';?>
<?php 
if(isset($_POST['datepicker']) && isset($_POST['submit']) && $_POST['submit'] =='Submit' ){
    $originalDate = $_POST['datepicker'];
    $newDate = date("Y-m-d", strtotime($originalDate));
    $qty = $_POST['qty'];
    $pot_size = $_POST['pot_size'];
    $name = $_POST['name'];
    $soil = $_POST['soil'];
    $type = $_POST['type'];
    $thc = $_POST['thc'];
    $yield = $_POST['yield'];
    $ready = $_POST['ready'];
    $genetics = $_POST['genetics'];
    $datepicker = $newDate;

    // SQL query to insert a new crop
    $sql = "INSERT INTO `crop` (`qty`,`pot_size`,`name`,`soil`,`type`,`thc`,`yield`,`ready`,`genetics`,`startdate`) VALUES ('$qty','$pot_size','$name','$soil','$type','$thc','$yield','$ready','$genetics','$datepicker')";
    if (mysqli_query($conn, $sql)) {
        echo "New crop record created successfully";
        $crop_id = mysqli_insert_id($conn);

        // SQL query to associate the crop with the current user
        $user_id = $_SESSION['user_id'];
        $sql = "INSERT INTO `user_crop` (`user_id`, `crop_id`) VALUES ('$user_id', '$crop_id')";
        if (mysqli_query($conn, $sql)) {
            echo "Crop associated with user successfully";
        } else {
            echo "Error: " . $sql . "<br>" . mysqli_error($conn);
        }
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
}
?>

解决方案 #

1. 修正作物数据查询逻辑（核心问题） #

所有用户看到相同数据的根本原因是查询crop表时没有通过user_crop关联表筛选当前用户的专属数据。你需要修改查询代码，加入用户ID的过滤条件：

-- 查询当前用户的最新专属作物
SELECT c.* 
FROM crop c
JOIN user_crop uc ON c.id = uc.crop_id
WHERE uc.user_id = ?  -- 这里的?是当前登录用户的ID
ORDER BY c.startdate DESC 
LIMIT 1;

在PHP中实现时，必须使用预处理语句防止SQL注入，示例代码：

session_start(); // 确保会话已启动
$user_id = $_SESSION['user_id'];

// 预处理查询
$stmt = $conn->prepare("SELECT c.* FROM crop c JOIN user_crop uc ON c.id = uc.crop_id WHERE uc.user_id = ? ORDER BY c.startdate DESC LIMIT 1");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();
$user_crop = $result->fetch_assoc();

// 输出用户专属的作物数据
if($user_crop) {
    // 处理并显示数据
} else {
    echo "暂无专属作物";
}

2. 确保会话用户ID有效 #

• 在所有需要获取用户数据的页面开头必须添加session_start();，否则无法读取$_SESSION['user_id']。
• 增加登录状态校验，避免未登录用户访问数据页面：

  session_start();
  if(!isset($_SESSION['user_id'])) {
      header("Location: login.php");
      exit;
  }
  

3. 修复创建作物代码的潜在问题 #

现有创建作物的代码存在SQL注入风险，同时需要确保会话已启动：

<?php 
session_start(); // 新增：启动会话
include 'include/dbconnect.php';

if(isset($_POST['datepicker']) && isset($_POST['submit']) && $_POST['submit'] =='Submit' ){
    $originalDate = $_POST['datepicker'];
    $newDate = date("Y-m-d", strtotime($originalDate));
    $qty = $_POST['qty'];
    $pot_size = $_POST['pot_size'];
    $name = $_POST['name'];
    $soil = $_POST['soil'];
    $type = $_POST['type'];
    $thc = $_POST['thc'];
    $yield = $_POST['yield'];
    $ready = $_POST['ready'];
    $genetics = $_POST['genetics'];
    $datepicker = $newDate;
    $user_id = $_SESSION['user_id'];

    // 使用预处理语句插入作物
    $stmt = $conn->prepare("INSERT INTO `crop` (`qty`,`pot_size`,`name`,`soil`,`type`,`thc`,`yield`,`ready`,`genetics`,`startdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("iiiiiiisss", $qty, $pot_size, $name, $soil, $type, $thc, $yield, $ready, $genetics, $datepicker);
    
    if ($stmt->execute()) {
        echo "New crop record created successfully";
        $crop_id = $stmt->insert_id;
        $stmt->close();

        // 预处理语句关联用户和作物
        $stmt = $conn->prepare("INSERT INTO `user_crop` (`user_id`, `crop_id`) VALUES (?, ?)");
        $stmt->bind_param("ii", $user_id, $crop_id);
        
        if ($stmt->execute()) {
            echo "Crop associated with user successfully";
        } else {
            echo "Error: " . $stmt->error;
        }
        $stmt->close();
    } else {
        echo "Error: " . $stmt->error;
    }
}
?>

4. 验证数据关联正确性 #

手动查询user_crop表，确认每个作物ID都正确关联到对应的用户ID，避免出现关联错误导致的数据混乱：

SELECT uc.user_id, uc.crop_id, c.name FROM user_crop uc JOIN crop c ON uc.crop_id = c.id;

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