MySQL insert incorrect integer value for column at row 1

Question

I have to insert in column field unothree either a Null or an Integer value.
Only the integer value inserts but not the null value

-- Create table
$sqlquery="CREATE TABLE numberone(uno_ID int NOT NULL AUTO_INCREMENT, 
unoone VARCHAR(50) NOT NULL,
unotwo VARCHAR(50) NOT NULL,
unothree MEDIUMINT(8) UNSIGNED,
unofour VARCHAR(10) NOT NULL,
unofive VARCHAR(20) NOT NULL,
PRIMARY KEY(uno_ID ))";

$sqlquery="INSERT INTO numberone(unoone, unotwo, unothree, unofour, unofive)
VALUES ('$one', '$two', '$three', '$four', '$five')";

Could you write more closely how you try to insert NULL value? — user2941651
– user2941651, Commented Dec 4, 2014 at 20:53

Mike · Accepted Answer · 2014-12-04 22:16:45Z

1

$three = $three ? "'$three'" : "NULL";
$sqlquery="INSERT INTO numberone(unoone, unotwo, unothree, unofour, unofive)
VALUES ('$one', '$two', $three, '$four', '$five')";

In other words, move the quotes out of the final query so that the value is either quoted or NULL. In fact, since it's an integer, you don't even need the quotes but I figured you wanted a more generic answer.

answered Dec 4, 2014 at 22:16

Mike

2,7711 gold badge18 silver badges21 bronze badges

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

5 Comments

Marc Over a year ago

Thanks Mike! Error: Incorrect integer value: 'NULL' for column 'unothree' at row 1

Mike Over a year ago

You've probably left the quotes in the values string so it's trying to add 'NULL' instead of NULL.

Marc Over a year ago

Thanks! It works but I have a duplicate insert of the record. In the duplicate record all the fields are blank save for unothree which shows NULL. Moreover if this has anything to do with it // escape variables for security $three = mysqli_real_escape_string($con, $_POST['three']); $four = mysqli_real_escape_string($con, $_POST['four']); $five = mysqli_real_escape_string($con, $_POST['five']);

Mike Over a year ago

Escape the string before checking and replacing it with NULL. One insert statement with one set of data should = one insert. What do you get when you "echo $sqlquery" before executing it?

Marc Over a year ago

Its a blue sky scenario - profound thanks Mike! Just had the curly braces in the wrong place. Though the echo "1 record added" after refresh only

Collectives™ on Stack Overflow

MySQL insert incorrect integer value for column at row 1

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related