poleposters Posted February 26, 2010 Share Posted February 26, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/ Share on other sites More sharing options...
developerdave Posted February 26, 2010 Share Posted February 26, 2010 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') Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018528 Share on other sites More sharing options...
PFMaBiSmAd Posted February 26, 2010 Share Posted February 26, 2010 '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')"; Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018533 Share on other sites More sharing options...
poleposters Posted February 26, 2010 Author Share Posted February 26, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018741 Share on other sites More sharing options...
ialsoagree Posted February 26, 2010 Share Posted February 26, 2010 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] Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018744 Share on other sites More sharing options...
poleposters Posted February 26, 2010 Author Share Posted February 26, 2010 Sorry, that's not working either Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018747 Share on other sites More sharing options...
Psycho Posted February 27, 2010 Share Posted February 27, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018761 Share on other sites More sharing options...
poleposters Posted February 27, 2010 Author Share Posted February 27, 2010 No Luck again. It still inserts '0' instead of NULL even though the variable is set to NULL. Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018773 Share on other sites More sharing options...
PFMaBiSmAd Posted February 27, 2010 Share Posted February 27, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018806 Share on other sites More sharing options...
poleposters Posted February 27, 2010 Author Share Posted February 27, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018829 Share on other sites More sharing options...
PFMaBiSmAd Posted February 27, 2010 Share Posted February 27, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018869 Share on other sites More sharing options...
PFMaBiSmAd Posted February 27, 2010 Share Posted February 27, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/193471-problem-working-with-null/#findComment-1018901 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.