[RESOLVED] Insert NULL not working on Localhost

NevadaSam

This works fine on my hosting site and on another PC with apache and WinXP. But on my lap top where I also installed Apache on XP I get this error:

Error code 1366: Incorrect integer value: 'null' for column 'sequence_number' at row 1

This is the query I sent:

$SendQuery = "INSERT INTO Photo_sequence VALUES('null')";

This is the table I created:

CREATE TABLE Photo_sequence (
sequence_number int(11) NOT NULL auto_increment,
PRIMARY KEY (sequence_number))

Any ideas? Keep in mine this works on other PC's and on the internet. What could be wrong on my LapTop?

Sam
;

NogDog

Do not quote the value null. (If you do, then you are trying to insert the string 'null' into an integer field instead of the special (non)value represented by the SQL keyword NULL.)

NevadaSam

Ok. That works. I tried before to use : VALUES(' ')
But I see I needed to have a space between the quotes.

My LapTop has MySQL client version: 5.0.22, I guess the other PC and internet has an eairler version.

Thanks for all the help.

Weedpacket

Ah, MySQL and it's happy-go-lucky freewheeling we-don't-need-no-steenking-data-integrety ways. "It's an integer field, but you're trying to insert the string 'null'. I know! I'll put NULL in there instead That's close enough!"

Roger_Ramjet

Except that it is an autoinc NOT NULL column and should be throwing an exception when you try to insert NULL.

NevadaSam

Thanks for looking at this. I found out that I can not have quotes around the word NULL. Strange how it worked on other systems. Must be the newer version of MySql on my notebook that no longer accepts that.

Problem Solved

Sxooter

Technically, the two "proper" ways to do this are:

Leave out the autoinc field in the field list:
insert into table (field1, field2) values ('textvalue1',99);

Include the field in the list, use DEFAULT keyword:
insert into table (autoincfield, field1, field2) values (DEFAULT, 'textvalue1',99);

Note that either way, you should ALWAYS include a field list. Not including one is bad form (much worse than select * from table, which is also bad form) as the field order might get changed someday and your code will all break.

Note also that prior to about MySQL 4.1 or so, the DEFAULT keyword was not supported, so the first method, where you simply leave out the autoinc field from your fieldlist is the more portable method.

NevadaSam

The table I am using for this is just a one column table to get a unique sequence number to append to the end of a file name in order to avoid duplicates.

I changed it to:
INSERT INTO Photo_sequence VALUES(null)

I understand you are saying I can also use:

INSERT INTO Photo_sequence VALUES(autoincfield)

INSERT INTO Photo_sequence VALUES()

Sxooter

NevadaSam wrote:
The table I am using for this is just a one column table to get a unique sequence number to append to the end of a file name in order to avoid duplicates.

I changed it to:
INSERT INTO Photo_sequence VALUES(null)

I understand you are saying I can also use:

INSERT INTO Photo_sequence VALUES(autoincfield)

or

INSERT INTO Photo_sequence VALUES()

Well, what I suggested may not work. What my first suggestion was was to use the keywork DEFAULT not autoincfield. With 5.0.x here's what I can do:

create table aitest (id int not null AUTO_INCREMENT primary key);
insert into aitest () values ();
Query OK, 1 row affected (0.00 sec)
insert into aitest (id) values (DEFAULT);
Query OK, 1 row affected (0.00 sec)
select * from aitest;
+----+
| id |
+----+
| 1 |
| 2 |
+----+