'Incorrect integer value' for an SQL Query inside a PHP script

Question

The following query works fine in SQL but not as an SQL Query in PHP for some reason. The error message is as follows:

Incorrect integer value: 'SELECT id FROM colours WHERE colour = ' for column databasename.numbers.col_id at row 1

$redID = "SELECT id FROM colours WHERE colour = $red";

$populate = "INSERT INTO numbers (col_id) VALUES ('$redID')";

$result=$conn->query($populate);

Database Table - numbers

Database Table - colours

Learn to use parameters to pass values into queries.

Gordon Linoff
– Gordon Linoff

2020-04-25 12:05:47 +00:00
Commented Apr 25, 2020 at 12:05 — Gordon Linoff
– Gordon Linoff, Commented Apr 25, 2020 at 12:05

Nelson Rakson · Accepted Answer · 2020-04-25 22:28:46Z

1

Your populate query cannot accept an SQL String as a 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;
}

edited Apr 25, 2020 at 22:28

answered Apr 25, 2020 at 9:49

Nelson Rakson

5663 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr Man Over a year ago

Thank you for the above. Hwever I get the eroor 'Parse error: syntax error, unexpected '}' with this code. To fix it I added a ; after $redID=$redIDResult['id'] However, now I am getting the error 'Fatal error: Uncaught Error: Call to undefined method mysqli_result::fetch() in

Nelson Rakson Over a year ago

@MrMan Sorry, I assumed you use PDO connection. I've updated my answer.

Collectives™ on Stack Overflow

'Incorrect integer value' for an SQL Query inside a PHP script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related