Php/Mysql date saved as '0000-00-00'

Question

I have a Mysql database where i keep dates of the clients orders.

Date is generated by a php function date('Y-m-d'). Two records in my database have values '0000-00-00'. One was created by a client from Australia and another by a client from Italy. Other 5000 orders are fine.

My question is how is it possible to generate such date ? Could it be something with individual browser settings ?

Thanks for help

Ardellardella

Answer 1

The only reason what i can find is either your client has entered improper date format or he has entered an empty string. Remember the date format in Mysql is yyyy-mm-dd.

Bencher

Just to add to your knowledge, 'yyyy-mm-dd' in PHP is 'Y-m-d' – Sabellian

Answer 2

I noticed I was having the same exact issue, and it turned out it was simply that I didn't put single quotes around my $date variable when inserting it, thus it was inserting a null or blank value which appeared as 0000-00-00 in the database. Once I put '$date' instead of $date in my insert statement, the value appeared as expected.

Backstage

Thank you so much, I tried to solve this problem since this morning. I changed statement: "$DOT[0], $DOT[1], $date1, $date2" to "$DOT[0], $DOT[1], '$date1', '$date2'" (with single quotes around $date variables) and it worked. – Donal

+1 to this. I am relatively new to PHP and I spent a lot of time trying to figure this out. After stumbling across this comment it sorted it all out! – Susceptive

Answer 3

It means MySQL was passed an invalid value including no value at all. Check your code to make sure that it always has a valid value for that date.

Stutter

Everything looks fine. Client selects date in a date picker. He has no oportunity to provide an invalid date. I checked that earlier so i am looking for other solutions – Ardellardella

May be the client had JavaScript disabled. May be the JavaScript failed to load. May be the client knew how to bypass JavaScript validation. – Truth

Answer 4

Check this post default date '0000-00-00 00:00:00' or null. It will answer your question. Probably, year, month and day variables at their client side code have not been initialized.

EDIT If that's not the case, then it seems you have recently changed the Date-related column in the database from VARCHAR or TEXT to DATE.

MySql changes all NULL values in this case to '0000-00-00'. So, if some time back, date has not been a compulsory field on the UI and if it was passed as NULL, then the above thing has happened.

Sabellian

The date has always been a DATE. The first time i got a '0000-00-00' was about 12hours ago. Earlier everything was fine – Ardellardella

which library does the datepicker belong to? – Sabellian

Answer 5

It could be the case that you have purely programmed or outdated mysql function. For instance: CURDATE() + 5 will insert the date correctly in the DB, however if the user does it on 2016-12-28 the DB will show 0000-00-00. It will work fine if it the current date is 2016-12-20. The absolutely correct function would be CURDATE() + INTERVAL 5 DAY. You write at the beginning of Jan, so maybe u have similar issue.

Cozenage

Answer 6

When inserting a date into a MySQL database using PHP, one common reason for the date being saved as 0000-00-00 is incorrect formatting or type mismatch in the prepared statement. This issue can occur if the date is not properly converted to the correct format (e.g., 'YYYY-MM-DD') before insertion into the database or if the wrong data type is specified in the bind_param() function. In PHP's bind_param() function, dates should typically be bound as strings using the "s" format specifier, ensuring that they are treated as strings and inserted correctly into the database. Additionally, it's essential to ensure that the date being inserted is in a valid format recognized by MySQL to prevent unexpected behavior. Here's a shorter example illustrating how to insert a user's registration date into a MySQL database using PHP's mysqli with proper date formatting:

 $conn = new mysqli($servername, $username, $password, $dbname);
    

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
 
    $registrationDate = date('Y-m-d'); 
    $userID = 1;
    
    $stmt = $conn->prepare("INSERT INTO Users (UserID, RegistrationDate) VALUES (?, ?)");
    $stmt->bind_param("is", $userID, $registrationDate);
    
    
    if ($stmt->execute()) {
        echo "User registration date inserted successfully.";
    } else {
        echo "Error inserting user registration date: " . $conn->error;
    }
    
  
    $stmt->close();
    $conn->close();

 

Guzman