CODEWITHSUNDEEP
phpmysqldatenullsql-insert
ajor
ajor

Reputation: 1634

MySQL, how to insert null dates

I am having trouble inserting null values into date fields into a MySQL table.

Here is the insert query:

$query = 'INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES ("'.$string1.'", "'.$string2.'", '.$date1.', '.$date2.')';

Columns s1 and s2 take string values and d1 and d2 take dates. When I run this query with only the string fields, there is no problem.

The date values can be either set or null, so I have not included the quotation marks in the query, but have instead added them to the variable earlier on. This is the php code I am using to set the date values:

if (empty($date1)){
    $date1 = NULL;
}
else{
    $date1part = explode("/",$date1);
    $date1 = '"'.$date1part[2].'/'.$date1part[1].'/'.$date1part[0].'"';
}

When the date values are all set, the record is inserted correctly. However, when either of the dates is null, nothing is inserted.

Why can't I just insert null values into MySQL like this?

Upvotes: 16

Views: 72475

Answers (8)

norbert chitsike
norbert chitsike

Reputation: 11

Years later, if someone is still experiencing this issue, you want to use PDO and bind the variables, everything will be taken care of no need to handle the null variables yourself.

Upvotes: 0

Optimaz Prime
Optimaz Prime

Reputation: 937

If NULL does not work, just pass your date as "0000-00-00":

$chequeDate = "0000-00-00";

Upvotes: 3

Siva
Siva

Reputation: 1531

In Mysql DATE data type Default NULL means

Some version set as 0000-00-00

Some version set as 1970-01-01

Upvotes: 0

Grokking
Grokking

Reputation: 715

Probably answer is unneeded at this moment, but I found solution exactly I have been searching. Use an Expression to pass NULL like this: 

['some_date_to_update' => new Expression('NULL')]

Hence, MySQL will understand what you want, and save (NULL) in DB instead of storing 0-dates. Hope this will help somebody.

Upvotes: 1

Luis Daniel Dorta
Luis Daniel Dorta

Reputation: 11

In Derby, If you want to insert values except the ones you have declared Null (column_d1, column_d2), sql:

INSERT INTO DB.table (column_s1, column_s2) VALUES ('s1', 's2');

Upvotes: 1

innovative kundan
innovative kundan

Reputation: 631

Backslash N is another way to express NULL in MySQL.

Try putting the value (backslash N): \N into one of the parameters like this:

$data1 = "\N";
$sql="insert into tablename set column_s1='" . $data1 . 
  "', column_s2='" . data2 . 
  "', column_s3='" . $data3 . "'";

Reference: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 2

Timo Huovinen
Timo Huovinen

Reputation: 55693

You should convert the null variable into a NULL string first Like this:

if(is_null($date1)){
    $date1 = 'NULL';
}

If you are using a MySQL date column, you must also specify that it should hold null when creating it, like this:

CREATE TABLE `table` (
id INT NOT NULL AUTO_INCREMENT,
date DATE NULL DEFAULT NULL,
PRIMARY KEY(id)
)

It is also very important that you perform the query with bound parameters, for example using pdo

  1. http://www.php.net/manual/en/pdo.construct.php
  2. http://php.net/manual/en/pdo.prepared-statements.php
  3. How do I insert NULL values using PDO?

Something like this:

$query = 'INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES (?, ?, ?, ?)';
$stmt = $db->prepare($query);
$stmt->execute(array($string1,$string2,$date1,$date2));

Upvotes: 7

Wh1T3h4Ck5
Wh1T3h4Ck5

Reputation: 8509

Try this:

$query = "INSERT INTO table (column_s1, column_s2, column_d1, column_d2) 
          VALUES ('$string1', '$string2', " . ($date1==NULL ? "NULL" : "'$date1'") . ", " . ($date2==NULL ? "NULL" : "'$date2'") . ");";

so for example if you put this into query:

$string1 = "s1";
$string2 = "s2";
$date1 = NULL;
$date2 = NULL;

result should be:

INSERT INTO table (column_s1, column_s2, column_d1, column_d2) VALUES ('s1', 's2', NULL, NULL);

Upvotes: 14

Related Questions