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')";
To let MySql generate sequence numbers for an AUTO_INCREMENT
field you have three options:
...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 = ""
.
Source: Stackoverflow.com