2

I have birthday column as a date defined in my table. When i insert a person and left blank birthday field, i get 0000-00-00 and i want to get NULL values. when i insert date as birthday i get date, which is fine.

i tried :

Create table (
birthday DATE NULL,
birthday DATE DEFAULT NULL,
birthday DATE NULL DEFAULT NULL,
);

doesn't work, please a little advice to get NULL instead of 0000-00-00 in my birthday fields. my php script:

$q = "INSERT INTO users (birthday, other_column, data_registration) VALUES ('$birthday', '$other_column', NOW())

i get no solution yet, any ideea please?

  • 4
    You define a 3 column with same name in one table? – Jens May 21 '15 at 08:11
  • If you want the value to be `NULL` then pass `NULL` in your SQL query (and not quoted in a string) – Rowland Shaw May 21 '15 at 08:13
  • i must used a quote in a string –  May 21 '15 at 08:15
  • i have 105 columns in my table, here i write an example. –  May 21 '15 at 08:16
  • 3
    You have 105 columns in your table? We need to talk about your table. – Strawberry May 21 '15 at 08:42
  • not all the time i need to be NULL, just when i miss/left birthday blank –  May 21 '15 at 08:48
  • what about my table? has 106 columns, 4200 records and about 12 fields as date type which has the same problem. in my fields(as date) i get 0000-00-00 when i not introduce any date and i need to get blank (NULL) –  May 21 '15 at 08:58

3 Answers3

3

What is in your $birthday?

Suppose you have $birthday = null or $birthday = "null", you will get 0000-00-00. This is because when it's passing to your query, it becomes something like (I believe it's treating your 'null' or null as a string, because you have single quotes around it):

INSERT INTO users (birthday, other_column, data_registration) VALUES ('null', '$other_column', NOW())

Instead try this, which the 2 single quotes around $birthday are removed:

$q = "INSERT INTO users (birthday, other_column, data_registration) VALUES ($birthday, '$other_column', NOW())

Here is an alternative fix: 

if ($birthday) {
$q = "INSERT INTO b (birthday, other_column, data_registration) VALUES ('$birthday', '$other_column', NOW())";
} else {
$q = "INSERT INTO b (birthday, other_column, data_registration) VALUES (DEFAULT, '$other_column', NOW())";
}
jsuen
  • 112
  • 2
  • 8
  • $dayn=$_POST['dayn']; $monthn=$_POST['monthn']; $yearn=$_POST['yearn']; $birthday = $yearn.'-'.$monthn.'-'.$dayn; –  May 21 '15 at 10:52
1

if you use birthday DATE DEFAULT NULL, in your create statemnt and use 

INSERT INTO users (other_column) VALUES ( '$other_column')

You should get null´inbirthday` column.

Jens
  • 67,715
  • 15
  • 98
  • 113
  • i get 0000-00-00, and i want NULL value, i defined NULL DEFAULT NULL in birthday statement when i create the table –  May 21 '15 at 08:18
0

If you want to EXPLICITLY insert null into the table, then you will need to insert NULL from PHP.

If you want to leave the table to assume its default value, then either OMIT the column from the insert clause entirely, or otherwise use the literal DEFAULT which will apply the default.

e.g.

CREATE TABLE Person
(
    Name VARCHAR(20),
    BirthDate DATE DEFAULT NULL -- Redundant, since a NULLABLE column will default NULL
);

INSERT INTO Person(Name) VALUES('Joe'); -- Default, NULL
INSERT INTO Person(Name, BirthDate) VALUES('Joe', NULL); -- Explicit Null
INSERT INTO Person(Name, BirthDate) VALUES('Joe', DEFAULT); -- Default, NULL

SqlFiddle example here

If the date in PHP you are inserting is 0000-00-00, then you will need to apply logic in your code to either substitute NULL, DEFAULT for the column, or omit the column (or I guess use a trigger, but this is a hack)

StuartLC
  • 104,537
  • 17
  • 209
  • 285