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

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

子查询部分总是在().&nbsp;并根据您的需要检查 WHERE 条件：INSERT&nbsp;INTO&nbsp;spinner(city_id)&nbsp;VALUES((SELECT&nbsp;city_id&nbsp;FROM&nbsp;city&nbsp;WHERE&nbsp;city_name&nbsp;=&nbsp;"$city_name"))

Answer 2

你忘记了设定你的价值观。你的语法应该是这样的INSERT&nbsp;INTO&nbsp;spinner(city_id)&nbsp;VALUES((SELECT&nbsp;city_id&nbsp;FROM&nbsp;city&nbsp;WHERE&nbsp;city_name&nbsp;=&nbsp;'$city_name'))只是一个快速评论，我会亲自将我的查询分开。我不会嵌套它们