php - 如何在子表php中插入外键

Question

我有两个表 users 和 requests。 Users 表 包含列:id、username、password 和town。我可以在 users 中成功插入数据。 requests 表有:id、user_id、product_name、proposed_price 和 request_description，其中 user_id 是引用 users 表中的 id 的外键。问题是在以 user_id 作为外键的 requests 表中插入数据失败。我得到一个错误:

Undefined variable: user_id and mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement on line : $qry->bind_param("i", $user_id);

这个函数应该用于插入:

public function  User_request ($product_name, $proposed_price, $request_description) {

     $qry = $this->conn->prepare("SELECT id FROM users WHERE id = '$user_id' ");
     $qry->bind_param("i", $id);
     $result= $qry->execute();
     $user_id = $qry->num_rows();
     $qry->close();

    if($user_id > 0){

    $stmt = $this->conn->prepare("INSERT INTO  requests (user_id, product_name, proposed_price, request_description) VALUES(?, ?, ?, ?)");

    $stmt->bind_param("sss",$user_id, $product_name, $proposed_price, $request_description);
    $result = $stmt->execute();
    $stmt->close();

    // check for successful store
    if ($result) {
        $stmt = $this->conn->prepare("SELECT * FROM requests WHERE request_description = ?");
        $stmt->bind_param("s", $request_description);
        $stmt->execute();
        $user = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return $user;
    } else {
        return false;
    }

    }
} 

下面的代码调用上面的函数:

<?php

include './DbHandler.php';
$db = new DBHandler();

// json response array
$response = array("error" => FALSE);

if ( isset($_POST['product_name']) && isset($_POST['proposed_price']) &&        isset($_POST['request_description']) ) {

// receiving the post params
$product_name = $_POST['product_name'];
$proposed_price =$_POST['proposed_price'];
$request_description =$_POST['request_description'];

    // create a new request
    $user = $db-User_request($product_name, $proposed_price,    $request_description);
    if ($user) {
        // user stored successfully
        $response["error"] = FALSE;
        $response["user"]["username"] = $user["username"];
        $response["user"]["proposed_price"] = $user["proposed_price"];
        $response["user"]["request_description"] = $user["request_description"];

        echo json_encode($response);
    } else {
        // user failed to store
        $response["error"] = TRUE;
        $response["error_msg"] = "oops error occured!";
        echo json_encode($response);
    }
}
   else {
     $response["error"] = TRUE;
      $response["error_msg"] = "Required parameters are missing!";
     echo json_encode($response);
   }
  ?>

Answer 1

相应地重写User_request()函数:

public function  User_request ($product_name, $proposed_price, $request_description) {

    $qry = $this->conn->prepare("SELECT id FROM users WHERE id = ? ");
    $qry->bind_param("i", $user_id);
    $result= $qry->execute();
    $user_id = $qry->num_rows();
    $qry->close();

    if($user_id > 0){

        $stmt = $this->conn->prepare("INSERT INTO requests (user_id, product_name, proposed_price, request_description) VALUES(?, ?, ?, ?)");

        $stmt->bind_param("isss", $user_id, $product_name, $proposed_price, $request_description);
        $result = $stmt->execute();
        $stmt->close();

        // check for successful store
        if ($result) {
            $stmt = $this->conn->prepare("SELECT * FROM requests WHERE request_description = ?");
            $stmt->bind_param("s", $request_description);
            $stmt->execute();
            $user = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            return $user;
        } else {
            return false;
        }
    }
} 

注意:
您应该在准备语句中使用占位符 ?:$this->conn->prepare("SELECT id FROM users WHERE id = '$user_id' "); 代替 '$user_id'，因为你有 $qry->bind_param("i", $user_id).
$stmt->bind_param("sss",$user_id, $product_name, $proposed_price, $request_description);中出现类似错误，将"sss"替换为"iss".