eurob Posted January 15, 2009 Share Posted January 15, 2009 mytable in mysql create table mytable ( id int, topic, startdate datetime default NULL) ) A user can submit a form without filling out a value for the startdate. I am using a function to pull out a NULL, the NULL value in PHP, however, shows nothing, so I cannot use it directly in mysql function testfornull($tst){ if($tst=='')return NULL; return $tst; } -- > function returns ''" in case of a NULL In the case of a NULL value, my sql statement should look like this insert into mytable(id,topic,startdate) values('some topic',NULL) How should I go about with this?( I cannot put NULL between quotes, it will error in mysql) Quote Link to comment Share on other sites More sharing options...
RestlessThoughts Posted January 15, 2009 Share Posted January 15, 2009 From my understanding the mysql database should read NULL (without quotes) or '' as equal to NULL or nothing just fine, the same as it handles not putting in a value for the ID just fine. Quote Link to comment Share on other sites More sharing options...
eurob Posted January 15, 2009 Author Share Posted January 15, 2009 I can omit it indeed just like the value for id but then I have to make separate sql statements when NULL values are involved, which is really annoying. '' does not equate to NULL in mysql, when I use it, mysql throws an error. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted January 15, 2009 Share Posted January 15, 2009 This should work: insert into mytable(topic,startdate) values('some topic','') Mysql should read it as null. Also you will notice that i omitted the id. If you set the id as primary key and auto_increment you do not need to have it in the query. It will automatically do what it needs to with it. edit: actually thinking again what are you storing startdate as. if it is datetime i think it only accepts NULL or an actual value as well as some others such as date. Quote Link to comment Share on other sites More sharing options...
RussellReal Posted January 15, 2009 Share Posted January 15, 2009 you can use pack to pack a NULL byte into a string.. but returning NULL or just using "null" in your mysql query should be enough to satisfy mysql =\ actually you don't even have to SPECIFY the field if it will be null Quote Link to comment Share on other sites More sharing options...
corbin Posted January 15, 2009 Share Posted January 15, 2009 mytable in mysql function testfornull($tst){ if($tst=='')return NULL; return $tst; } -- > function returns ''" in case of a NULL No, it's returning PHP's version of null, which is converted to '' when casted to a string. If you want to do it the long way: $q = "INSERT INTO sometable VALUES (" . ((is_null($var)) ? 'NULL' : '\'' . mysql_real_escape_string($var) . '\'' ). ");" If you want to do it the lazy way (I would be tempted to do this if I were going to do multiple null columns): function quote($val) { if(is_null($val)) return 'NULL'; return '\'' . mysql_real_escape_string($val) . '\''; } Quote Link to comment Share on other sites More sharing options...
eurob Posted January 17, 2009 Author Share Posted January 17, 2009 I tried all the solutions but without success. There is just no way of passing a real NULL value to mysql. If there is please give me a working example as I would be very interested in that. What I do now is construct the sql statement with fields that do have a value such as this: if(!empty($mydate))$sql.",startdate='$startdate"; That way the fields that are empty will be omitted. Also you cannot in mysql do something like this: insert into mytable(startdate) values(''); because the startdate is a datetime field you have to either omit it or do it like this insert into mytable(startdate) values(null); Quote Link to comment 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.