how to parse a real NULL value from PHP to mysql

[eurob]

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)

 

 

[RestlessThoughts]

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.

[eurob]

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.

[ngreenwood6]

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.

[RussellReal]

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

[corbin]

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) . '\'';

}

[eurob]

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);