php - 如何将主键作为外键插入到另一个表中？

Question

我有两张 table tbl_cars 和 tbl_user其中tbl_user将userID作为主键我将其声明为我的 tbl_cars 上的外键每当用户登录时，它无法将项目发布到 tbl_cars 我收到此错误

Cannot add or update a child row: a foreign key constraint fails (u850332371_car.tbl_cars, CONSTRAINT tbl_cars_ibfk_1 FOREIGN KEY (userID) REFERENCES tbl_user (userID))

这是我的插入代码。

插入.php

<?PHP
$conn = new mysqli('******', '******', '******', '******');

 if ($conn->connect_error) {
 die("Connection failed: " . $conn->connect_error);
 }

error_reporting(E_ALL);
ini_set('display_errors',1);// at top of page

if(isset($_POST['txtCarModel']) && isset($_POST['txtCarType']) && 
isset($_POST['txtCapacity']) && isset($_POST['image']) &&
isset($_POST['txtFuelType']) && isset($_POST['txtPlateNumber'])){

$now = DateTime::createFromFormat('U.u', microtime(true));
$id = $now->format('YmdHis');

$upload_folder = "upload";
$path = "$upload_folder/$id.jpeg";
$image = $_POST['image'];
$fullpath = "http://carkila.esy.es/$upload_folder/$id.jpeg";

$Car_Model = $_POST['txtCarModel'];
$Car_Type = $_POST['txtCarType'];
$Capacity = $_POST['txtCapacity'];
$Fuel_Type = $_POST['txtFuelType'];
$PlateNumber = $_POST['txtPlateNumber'];
$Image = $_POST['image'];


$stmt = $conn->prepare("INSERT INTO tbl_cars (Car_Model, Car_Type, Capacity, fuelType, carPlatenuNumber, Image) VALUES (?, ?, ?,?,?,?)");
$query = "INSERT INTO tbl_cars(Car_Model, Car_Type, Capacity,fuelType, carPlatenuNumber, Image) 
VALUES ('$Car_Model', '$Car_Type', $Capacity, '$Fuel_Type', '$PlateNumber', '$fullpath')"; 

$stmt->bind_param("ssssss", $Car_Model, $Car_Type, $Capacity,$Fuel_Type,$PlateNumber, $fullpath);
$result = $stmt->execute();

if($result === false ) {
  die('execute() failed: ' . htmlspecialchars($stmt->error));
}else{
echo "New records created successfully";
}

$stmt->close();
$conn->close();

    }


?>

更新

这是我的 session 登录信息。我希望 userID 出现在向数据库插入数据时。

登录.php

<?php 
require 'database-config.php';

session_start();

$username = "";
$password = "";

if(isset($_POST['username'])){
$username = $_POST['username'];
}
if (isset($_POST['password'])) {
$password = $_POST['password'];

}


$q = 'SELECT * FROM tbl_user WHERE username=:username AND password=:password';

$query = $dbh->prepare($q);

$query->execute(array(':username' => $username, ':password' => $password));


if($query->rowCount() == 0){
header('Location: index.php?err=1');
}else{

$row = $query->fetch(PDO::FETCH_ASSOC);

session_regenerate_id();
 $_SESSION['sess_user_id'] = $row['userID'];
 $_SESSION['sess_username'] = $row['username'];
 $_SESSION['sess_userrole'] = $row['roles'];

 echo $_SESSION['sess_userrole'];
session_write_close();

if( $_SESSION['sess_userrole'] == "renter"){
echo "owner";

}else if ($_SESSION['sess_userrole'] == "owner"){
echo"renter";

}


}


?>

谢谢你们。 :)

Answer 1

whenever a user logs in it can't post an item...

由于您知道哪个用户正在尝试向 tbl_cars 添加记录，因此请在插入内容中包含 userID。

$userID = ... //<- put the user id in this variable
$sql = 'INSERT INTO tbl_cars ('.
       'userID,Car_Model,Car_Type,Capacity,fuelType,carPlatenuNumber,Image'.
       ') VALUES (?, ?, ?, ?, ?, ?, ?)';

$stmt = $conn->prepare($sql);
$stmt->bind_param("sssssss", $userID $Car_Model, $Car_Type, $Capacity,
                             $Fuel_Type,$PlateNumber, $fullpath);
$result = $stmt->execute();

我相信您的问题是 userID 是必填字段，但是当您不提供默认值时，数据库无法为您插入默认值，因为该值必须绑定(bind)到中的主键tbl_user