Problem working with NULL

[poleposters]

Hi,

 

SOmething strange seems to be happening when I try to run this query. I want to insert NULL as the customer_id, however the following query inserts '0'

 

$customer_id=NULL;

$query="INSERT INTO tasks (task_id,task,customer_id) VALUES ('','$task','$customer_id')

 

The column has NULL set as the default.

 

If I replace the variable $customer_id with NULL in my SQL it works fine.

 

I'm not sure whats going on. Any clues?

 

[developerdave]

When you assign a variable to null it is actually "false" try just using NULL in your query? or make your variable a string NULL

 

$customer_id='NULL';

$query="INSERT INTO tasks (task_id,task,customer_id) VALUES ('','$task','$customer_id');

 

OR

 

$query="INSERT INTO tasks (task_id,task,customer_id) VALUES ('','$task','NULL')

[PFMaBiSmAd]

'NULL' is a string containing the characters N, U, L, and L.

 

NULL (no quotes) is the keyword meaning a null value. Remove the single-quotes from around the value or if your intent is to get the database to use the default value for the column, one way would be to simply not list it in the columns/values -

$query="INSERT INTO tasks (task_id,task) VALUES ('','$task')";

[poleposters]

Thanks,

 

Except I should mention that the customer_id is not always NULL. Only in certain cases. Which means I need to assign NULL to the $customer_id variable rather than directly to the SQL.

 

The methods you've both offered work, however what I really need to do is assign NULL to the variable.

 

Any more ideas?

 

Thanks

[ialsoagree]

Exactly what's described, just modify how you write the SQL...

 

$query="INSERT INTO tasks (task_id,task,customer_id) VALUES ('','$task',".(($customer_id == NULL) ? $customer_id : "'$customer_id'").')';

 

[edit]Changed the code as I really screwed it up[/edit]

[poleposters]

Sorry, that's not working either 

[Psycho]

At some point in your script you you are making a decision to set the vlaue to null before you execur, correct? Just do something like this

 

  
$customer_id_sql = ($a==$b) ? "'{$customer_id}'" : 'NULL';
  
$query="INSERT INTO tasks (task_id,task,customer_id) VALUES ('', '{$task}', {$customer_id_sql})";
  

 

Just replace $a==$b with whatever condition you are using to determine whether you want a null value or not.

[poleposters]

No Luck again.

 

It still inserts '0'  instead of NULL even though the variable is set to NULL.

 

 

[PFMaBiSmAd]

The column has NULL set as the default.

 

I doubt it. What is your table definition and what is your code that is retrieving and displaying the value?

[poleposters]

Here's the SQL to create the table if it helps.

 

CREATE TABLE `tasks` (
  `task_id` int(10) NOT NULL auto_increment,
  `task` varchar(1024) NOT NULL,
  `customer_id` int(10) default NULL,
  `order_id` int(10) default NULL,
  `assigned_to` int(2) NOT NULL,
  `assigned_by` int(2) NOT NULL,
  `task_added` datetime NOT NULL,
  `due_date` datetime NOT NULL,
  `completed_date` datetime default NULL,
  `task_type_id` int(2) NOT NULL,
  PRIMARY KEY  (`task_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

 

I'm not retrieving and displaying the value. Just took a peek in PHPmyadmin and saw that '0' was being inserted instead of NULL.

 

[PFMaBiSmAd]

Based on your table definition, any query that does not supply values for the NOT NULL columns will fail (just tested), unless you use the IGNORE keyword or strict mode is off, in which case NULL's will be inserted into the default NULL columns and zero values will be inserted into the NOT NULL columns (just tested as well.) All the queries posted in this thread are likely failing and any rows you have that are showing you a zero value for the customer_id were probably already in your table from previous testing.

[PFMaBiSmAd]

The query that had 'NULL' (a string enclosed in single-quotes) would result in a zero being inserted into a numeric column because the string will be converted to the 'best' numeric value, a zero.