inserting null into a year field 1

[mufka]

I have a field with data type YEAR. It is defaulted to NULL. I'm trying to INSERT a NULL into the field but I get the message

Error: Incorrect integer value: 'NULL' for column

Shouldn't I be able to insert NULL? Otherwise, I'd have to alter the script to not touch that field if the value is empty.

 

[johnwm]

Can you show us the exact insert query you are using?

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[mufka]

The input is coming from a form.
The basic code for the insert is:

INSERT INTO TABLE (firstname, lastname, year) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[year]');

I also tried:

if (((int)$_POST['year']) == 0){	
	$_POST['year'] = 'NULL';
} else {
	$_POST['year'] = (int)$_POST['year'];
}

When the 'if' part is true, it tries to insert 'NULL' instead of NULL. The 'else' part works fine - it changes to int otherwise it will try to insert text in the field.

 

[r937]

'NULL' is not the same as NULL

'NULL' is a string

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mufka]

I am aware of that, but I cannot get it to pass through as just NULL.

 

[hvass]

if your column year is defined as having NULL then you would just go

INSERT INTO TABLE (firstname, lastname) VALUES ('$_POST[firstname]','$_POST[lastname]');

and the value of year would be [NULL]

(you should really think of a better name than year which is probably a reserved word)

 

[mufka]

That would be the solution that would involve rewriting the script to use one set of fields and values when there is a year specified and another set of fields and values when one is not set. That's what I meant when I said:

Otherwise, I'd have to alter the script to not touch that field if the value is empty.

I'd rather figure out how to pass the NULL than create another if loop to evaluate the contents of the post and then change the field set. I wouldn't really learn anything if I did it that way. I'd like to learn what I'm doing wrong.

Is there some change I can make to:

    $_POST['year'] = 'NULL';

to get $_POST['year'] to be just NULL instead of 'NULL'?

 

[vacunita]

This is more of a PHP question, than MYSQL, but basically you need to make PHP deliver the word NULL to the query so you'll have to do 2 things.

1. keep your post value equal to the string 'NULL', and

2. remove the single quotes around your value in the query.

INSERT INTO TABLE (firstname, lastname, year) VALUES ('$_POST[firstname]','$_POST[lastname]',$_POST[year]);

now this will only work if your year column is typed as integer. So that even if it has a value you don't require the quotes.

With that said, using the posted values directly in a query is a big security risk and will leave you exposed to things like SQL injection and other db attacks. So at the very least you should be cleansing your form variables before plugging them into a query.

You might find this interesting reading:

http://en.wikipedia.org/wiki/Sql_injection

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mufka]

These are private data entry forms. Shouldn't be a security risk. I do validation on any public forms. I would think that is sufficient, but I'll look into it again. Problem solved. Thanks.