PHP 脚本中 SQL 查询的'Incorrect integer value'

Question

以下查询在SQL中运行良好，但由于某些原因在PHP中不能作为SQL查询。错误消息如下：

不正确的整数值：列databasename的"SELECT id FROM colors WHERE colour="。CCD_ 2。第1行处的col_id

$redID = "SELECT id FROM colours WHERE colour = $red";
$populate = "INSERT INTO numbers (col_id) VALUES ('$redID')";
$result=$conn->query($populate);

数据库表-数字

数据库表-颜色

Answer 1

填充查询不能接受SQL字符串作为col_id。

$redIDSQL = "SELECT id FROM colours WHERE colour = '$red'";
$redIDQuery=$conn->query($redIDSQL);
$redIDArray=array();
while($redIDResult=mysqli_fetch_assoc($redIDQuery)){
$redIDArray[]=$redIDResult['id'];
}
if(count($redIDArray)>0){
$populateSQL = "INSERT INTO numbers (col_id) VALUES ('".implode("'),('",$redID)."')";
$populateQuery=$conn->query($populateSQL);
}else{
echo "redID could not be found with colour matching ".$red;
}