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?

Improve this question
7
  • 4
    You define a 3 column with same name in one table? Commented May 21, 2015 at 8:11
  • If you want the value to be NULL then pass NULL in your SQL query (and not quoted in a string) Commented May 21, 2015 at 8:13
  • i must used a quote in a string Commented May 21, 2015 at 8:15
  • i have 105 columns in my table, here i write an example. Commented May 21, 2015 at 8:16
  • 3
    You have 105 columns in your table? We need to talk about your table. Commented May 21, 2015 at 8:42

3 Answers 3

Reset to default
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())";
}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

$dayn=$_POST['dayn']; $monthn=$_POST['monthn']; $yearn=$_POST['yearn']; $birthday = $yearn.'-'.$monthn.'-'.$dayn;
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.

Improve this answer

1 Comment

i get 0000-00-00, and i want NULL value, i defined NULL DEFAULT NULL in birthday statement when i create the table
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)

Improve this answer

1 Comment

whats happend when i must insert a birthday value? it work's?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.