[mysql] Incorrect integer value: '' for column 'id' at row 1

I am trying to insert into my mySQL database. The first column is the 'id' column, since its an auto_increment field, I left it blank. For some reason, I am unable to insert and I am getting the error mentioned below. I appreciate any help with this.

I am getting the following error while trying to insert:

Incorrect integer value: '' for column 'id' at row 1

my query

$insertQuery = "INSERT INTO workorders VALUES('', '$priority', '$requestType', '$purchaseOrder', '$nte', '$jobSiteNumber')";

This question is related to mysql insert

The answer is


To let MySql generate sequence numbers for an AUTO_INCREMENT field you have three options:

  1. specify list a column list and omit your auto_incremented column from it as njk suggested. That would be the best approach. See comments.
  2. explicitly assign NULL
  3. explicitly assign 0

3.6.9. Using AUTO_INCREMENT:

...No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

These three statements will produce the same result:

$insertQuery = "INSERT INTO workorders (`priority`, `request_type`) VALUES('$priority', '$requestType', ...)";
$insertQuery = "INSERT INTO workorders VALUES(NULL, '$priority', ...)";
$insertQuery = "INSERT INTO workorders VALUES(0, '$priority', ...";

Try to edit your my.cf and comment the original sql_mode and add sql_mode = "".

vi /etc/mysql/my.cnf

sql_mode = ""

save and quit...

service mysql restart

This is because your data sending column type is integer and your are sending a string value to it.

So, the following way worked for me. Try with this one.

$insertQuery = "INSERT INTO workorders VALUES (
                    null,
                    '$priority',
                    '$requestType',
                    '$purchaseOrder',
                    '$nte',
                    '$jobSiteNumber'
                )";

Don't use 'null'. use it as null without single quotes.


For the same error in wamp/phpmyadmin, I have edited my.ini, commented the original :

;sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

and added sql_mode = "".