?
Solved

PHP how to set date value (mysql) to NULL

Posted on 2009-04-16
9
Medium Priority
?
470 Views
Last Modified: 2013-12-13
I have a mysql database where the inserted values are set to 0000-00-00 00:00:00 where I actually want NULL .I did the below but still get the date_value in the format 0000-00-00 00:00:00

    $this->fields['date_value'] =NULL;

    $insert_sql = "INSERT INTO {$this->table_name} ("
                . implode(",", array_keys($this->fields))
                . ") VALUES ('"
                . implode("','", array_values($this->fields))
                . "')";
0
Comment
Question by:micro_learner
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:asianandrew
ID: 24161105
Check your field settings. Does it allow null?
0
 

Author Comment

by:micro_learner
ID: 24161263
Yes it does.
The default is NULL and allows null's.
 
I am confused :-(
 
0
 
LVL 3

Expert Comment

by:asianandrew
ID: 24161326
I know this seems silly, but try:

$this->fields['date_value'] ="NULL";

As per http://bytes.com/groups/php/1146-insert-null-not-blank-into-mysql-php-script

I've had issues with not using double quotes before, so maybe this will work.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24161445
Even if you set the value to "NULL", it will be wrong here:

                . ") VALUES ('"
                . implode("','", array_values($this->fields))
                . "')";

All values gets quotes. You must change this. Maybe something like this:
foreach($this->fields as $key=>$value) {
  if($key=='date_value') $value = 'NULL';
  else $value = "'$value'";
}
 
$insert_sql = "INSERT INTO {$this->table_name} ("
                . implode(",", array_keys($this->fields))
                . ") VALUES ("
                . implode(",", array_values($this->fields))
                . ")";

Open in new window

0
 

Author Comment

by:micro_learner
ID: 24162127
Is there any bug in the above code because the Insertion fails ....
0
 
LVL 4

Expert Comment

by:Fugas
ID: 24162245
Could you post the the insert string here? Then you can use str_replace to replace 'null' with null without quotes, that should work.
0
 

Author Comment

by:micro_learner
ID: 24162560

This is mine ..which does not work (nulls are not eneterd)
 

 
 $this->fields['date_value'] =NULL;

$insert_sql = "INSERT INTO {$this->table_name} ("
. implode(",", array_keys($this->fields))
. ") VALUES ('"
. implode("','", array_values($this->fields))
. "')";
___________________________________
below si the one that I got in here in the above post ..but this returns an error (does not enter at all)
 
foreach($this->fields as $key=>$value) { if($key=='date_value') $value = 'NULL'; else $value = "'$value'"; }
 
$insert_sql = "INSERT INTO {$this->table_name} (" . implode(",", array_keys($this->fields)) . ") VALUES (" . implode(",", array_values($this->fields)) . ")";  
 
 
 ..how/where  do I use the str_replace function ? Thanks for all your help
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 24162680
The preparation of the fields was wrong, it did not save the result:
foreach($this->fields as $key=>$value) {
  if($key=='date_value') $value = 'NULL';
  else $value = "'$value'";
  $this->fields[$key] = $value;  # put value back into $this->fields
}

Open in new window

0
 

Author Closing Comment

by:micro_learner
ID: 31571140
Great
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question