Putting A Null Into A Mysql Table Using A Variable

[chop]

Is is possible to Insert a NULL value into a table by assigning it to a variable.

 

For a low level user I want the activated column to have the following inserted:

  $a = md5( uniqid( rand(), true ) ); // put 32 char code into column "activated" for email verification

 

But for a higher level user, I don't want them to have to verify their email address. So I thought I could set  $a = "NULL" or $a= NULL

 

I only end up with the word NULL instead a real null .

 

I also thought there might be away of setting the column to default to NULL. Then the md5 () would write over it and that would solve the problem. But I couldn't do that either.

 

Now I just have two separate INSERT queries under a conditional with a "SET activated=NULL" for one of them. It works, but I hate writing the code twice.

 

 

thanks,

chop

[Larry]

Yeah, this is surprisingly tricky. The heart of this is that a PHP NULL value is not the same as providing a NULL value in MySQL. What I've normally done is create a variable as either 

$var = "'value'";

or

$var = "NULL";

And then use 

SET activated=$var

 in the query. So the query becomes either

...SET activated=NULL...

or 

...SET activated='value'...

[chop]

Thanks, Larry -

 

I'm glad to hear you say it's tricky. I felt like I was asking another dumb question because it seemed like it should be easy. Too bad I just can't set NULL as a default for the whole column. That would make it easy, then write over it with the  md5() when it should be validated.

 

Anyway, I'll probably keep the code as 2 queries written under two conditionals. If I've learned nothing else about programming, it's that code should be easy to understand at first glance after not seeing it for a few months. It might come at the cost of a few extra lines of code but, if I am able to immediately see the logic, it's worth it.

 

P.S. My PHP6 and MySQL5 book is in several chunks now after having rebound it twice. I admit I've not been easy on it though. Hope to get a newer version some day when one comes out (??)

 

chop

[Larry]

What you have is the 3rd edition of that book. The fourth edition came out in 2012. I'm working on the 5th edition now, which will be out by the end of the year. Thanks!