将表中的查询结果保存到变量中，然后将其发送到另一个表中

Question

我有一个名为 city 的表，其中有 city_id 和 city_name 列。我想要的是我想从 city 表中查询 city_id 的值到另一个名为 spinner 的表中，该表也包含 city_id 列。两个 city_id 都具有相同的 int 数据类型。这是我的 php 代码示例<?php$response = array();include 'DBConnect.php';//Get the input request parameters// $inputJSON = file_get_contents('php://input');// $input = json_decode($inputJSON, TRUE); //convert JSON into array//Check for Mandatory parameters $city_name = $_POST['city_name'];// $city_id = $_POST['city_id'];// $city_query = "SELECT city.city_id FROM city INNER JOIN spinner ON city.city_id = spinner.city_id";// $sql = "SELECT city_id FROM city WHERE $city_name ";// $stmt = $con->query("SELECT city_id FROM city WHERE $city_name");// $city_id = $stmt;    // $insertQuery  = "INSERT INTO spinner(city_id) VALUES (?)";    $insertQuery = "INSERT INTO spinner(city_id) SELECT city_id FROM city WHERE $city_name";    if($stmt = $con->prepare($insertQuery)){        // $stmt->bind_param('s',$city_id);        $stmt->execute();        $response["status"] = 0;        $response["message"] = "city data sent";        $stmt->close();    }    else{        $reponse["message"] = "cannot send data";    }echo json_encode($response);?>

Answer 1

子查询部分总是在(). 并根据您的需要检查 WHERE 条件：

INSERT INTO spinner(city_id) VALUES((SELECT city_id FROM city WHERE city_name = "$city_name"))

Answer 2

你忘记了设定你的价值观。你的语法应该是这样的

INSERT INTO spinner(city_id) VALUES((SELECT city_id FROM city WHERE city_name = '$city_name'))

只是一个快速评论，我会亲自将我的查询分开。我不会嵌套它们