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

Question

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')"; 

Answer 1

That probably means that your id is an AUTO_INCREMENT integer and you're trying to send a string. You should specify a column list and omit it from your INSERT.

INSERT INTO workorders (column1, column2) VALUES ($column1, $column2) 

Answer 2

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', ...";