PHP PDO中的DATE_ADD sql

Question

I am trying to insert a new row in my database with a date and the same date plus 1 week.

I use PDO in php, don't know if it is relevant.

This is the code which fails:

$stmtEncInsert = $db->prepare("INSERT INTO test
                                        (id_Event,   NIF,  name , description,  image,  start_date, end_date) 
VALUES (:idOfEvent, :NIF, :name, :description, :image, :startDate, DATE_ADD(:date_end,INTERVAL 1 WEEK)
                                        )");

        $stmtEncInsert->execute(array(':idOfEvent' => $idOfEvent, ':NIF'=> $NIF, ':name' => $resEnc['name'], ':description' => $resEnc['description'],
                                      ':image' => $resEnc['image'], ':startDate' => $date_end, ':date_end' => $date_end));

I get some data from other queries (example: $resEnc['..']) but all data is ok. I tried to print in a file all data and all of them are ok, they exist.

The problem is that the last field (end_date) is always giving error, always says: you cannot leave this field empty...

I think it is problem from DATE_ADD or so, but I haven't found anything related to this.

I tested deleting DATE_ADD and just using the current_date and it works so I guess this is the problem.

Any idea?

I use MySQL as engine

This is the output of the exception given:

 Integrity constraint violation: 1048 Column 'end_date' cannot be null

$date_end contains 0000-00-00 it is a valid date and field is DATE type got from DB from another query

Answer 1

0000-00-00 is not a valid date. MySQL can be configured to store invalid or incomplete dates, but that doesn't make them valid. As such, attempting to add one week will not return a valid date but NULL:

mysql> SELECT DATE_ADD('0000-00-00', INTERVAL 1 WEEK);
+-----------------------------------------+
| DATE_ADD('0000-00-00', INTERVAL 1 WEEK) |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+
1 row in set, 1 warning (0.03 sec)

To sum up: as the error message explains, if you've designed end_date to be mandatory, it cannot be empty. Either assign it a proper value or allow it to be NULL.

---

Edit #1: If you want to force that '0000-00-00' plus 1 week equals '0000-00-00' you can do this:

COALESCE(DATE_ADD('0000-00-00', INTERVAL 1 WEEK), '0000-00-00')

IMHO, dealing with '0000-00-00' dates does not provide any benefit and makes everything more convoluted but it's your code anyway ;-)

Edit #2: Users don't type zeroes in field dates. Your PHP code needs to detect empty dates and insert NULL:

if( is_valid_date($_POST['start_date']) ){
    $start_date = $_POST['start_date'];
}else{
    $start_date = NULL;
}
$stmtEncInsert = $db->prepare("INSERT INTO test (start_date) VALUES (:start_date)");
$stmtEncInsert->execute(
    array('test_date' => $start_date),
);

But you have a database design problem: the end_date column is mandatory, yet users are not required to enter it.