MYSQL - PHP user login system not displaying individual crops for each user

Question

I'm attempting to develop a user login system using PHP, where each user is assigned their own table within a database named 'crop'. However, despite each user having their own table, all users are seeing the same 'last crop'. How can I fix this issue?

here is the code:

    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);

And the PHP to create new crop:

<?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);
    }
}
?>

Answer 1

Thank you all for the comments.

@Dharman - SQL Injections, not so hard to fix ... turned out :D

Fixed code:

    // Enable error reporting and logging
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('log_errors', 1);
ini_set('error_log', '/var/log/php_errors.log');

// Initialize the session if it hasn't been started already
if (session_status() !== PHP_SESSION_ACTIVE) {
    session_start();
}

// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}
if (isset($_POST['submit'])) {
// Validate and sanitize the user inputs
$qty = filter_input(INPUT_POST, 'qty', FILTER_SANITIZE_NUMBER_INT);
$pot_size = filter_input(INPUT_POST, 'pot_size', FILTER_SANITIZE_NUMBER_INT);
$name = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_STRING);
$soil = filter_input(INPUT_POST, 'soil', FILTER_SANITIZE_STRING);
$type = filter_input(INPUT_POST, 'type', FILTER_SANITIZE_STRING);
$thc = filter_input(INPUT_POST, 'thc', FILTER_SANITIZE_NUMBER_INT);
$yield = filter_input(INPUT_POST, 'yield', FILTER_SANITIZE_NUMBER_INT);
$ready = filter_input(INPUT_POST, 'ready', FILTER_SANITIZE_NUMBER_INT);
$genetics = filter_input(INPUT_POST, 'genetics', FILTER_SANITIZE_STRING);
$datepicker = filter_input(INPUT_POST, 'datepicker', FILTER_SANITIZE_STRING);

// SQL query to insert a new crop
$sql = "INSERT INTO `crop` (`qty`, `pot_size`, `name`, `soil`, `type`, `thc`, `yield`, `ready`, `genetics`, `startdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, 'iisssiiiss', $qty, $pot_size, $name, $soil, $type, $thc, $yield, $ready, $genetics, $datepicker);
if (mysqli_stmt_execute($stmt)) {
    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['id'];
    $sql = "INSERT INTO `user_crop` (`user_id`, `crop_id`) VALUES (?, ?)";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, 'ii', $user_id, $crop_id);
    if (mysqli_stmt_execute($stmt)) {
        echo "Crop associated with user successfully<br>";
    } else {
        $error_msg = "Error associating crop with user: " . mysqli_error($conn);
        error_log($error_msg);
        echo "Error associating crop with user";
    }
} else {
    $error_msg = "Error creating new crop record: " . mysqli_error($conn);
    error_log($error_msg);
    echo "Error creating new crop record: " . mysqli_error($conn);
 }
}

@ADyson -

...it's not clear how you are defining "last crop", or what code you are using to calculate and display that information to the users.

You were very right here! I did not think that I must look at the way I present the data to each user. My approach:

$user_id = $_SESSION['id'];

$fetchqry = "SELECT c.*, DATE_FORMAT(c.startdate, ' %d/%m/%Y') AS startdate  
             FROM crop c
             JOIN user_crop uc ON c.id = uc.crop_id
             WHERE uc.user_id = $user_id
             ORDER BY c.id DESC 
             LIMIT 1";
$result = mysqli_query($conn, $fetchqry);

if ($result && mysqli_num_rows($result) > 0) {
    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
} else {
    // handle case where no crop is found for the current user
}
?>

All this sorted my problems. For now :) Thank you All!