0

I am working on a PHP - MySQL application where I have a requirement to convert all stored non date values to NULL in a DATE column and in future avoid insertion of 0000-00-00 values to column.In short a DATE column should only have either valid DATE values or NULL.Suppose I have a table with a column type as DATE and default value to NULL.When I tried to insert data into these column with a non date value, instead of setting the value to NULL the column is set as '0000-00-00'.So if I have the date value as an empty string or a non valid date string I don't want the value '0000-00-00' to get inserted.I am using a MySQLi wrapper class library to connect PHP application with MySQL database.The link is given below.

https://github.com/ThingEngineer/PHP-MySQLi-Database-Class

It is difficult for me to check at each insert/update operation in the application where the date column value is indeed a date value and if not, unset the column or set value of column to NULL.So I tried to do something at the place where the insert/update command used by the library (All insert/update commands pass through this library).But there also I should have some predefined information on what tables and what columns are DATE type (Probably has to fetch table meta information from information Schema table) at hand to compare and act accordingly.

I have found some similar functionality already in JDBC library like in the thread (Please see the answer with 5 upvotes).

https://dba.stackexchange.com/questions/139896/null-instead-0000-00-00-000000-in-mysql-5-7

So is there anything similar like zeroDateTimeBehaviour = convertTONull option in JDBC Driver that we can make use of in PHP - MYSQL connection interface or some conversion thing at DB level.

I am using PHP 5.6 and MySQL Version 5.6.

Edit - Many pointed out the question already has an answer in

MySQL, how to insert null dates

But all the answers do not lead to correct solution (not in my case at least) as many answers explains how to tackle problem at each insert/update level but not when the application already has a lot of insert/update commands and I want to modify the current application in least possible effort.I am looking to something like

zeroDateTimeBehaviour = convertTONull option in JDBC Driver

Kiran Muralee
  • 2,068
  • 2
  • 18
  • 25
  • 1
    Side note: Some systems provide `0000-00-00`, others `1970-01-01` – Justinas Jan 17 '19 at 08:12
  • In this case defaults to 0000-00-00 – Kiran Muralee Jan 17 '19 at 08:13
  • Not answering your question right now (might have a stab at it later if I have time though), but were you aware that PHP 5.6 went end-of-life 17 days ago? – Matt Jan 17 '19 at 08:18
  • Possible duplicate of [MySQL, how to insert null dates](https://stackoverflow.com/questions/17784390/mysql-how-to-insert-null-dates) – Justinas Jan 17 '19 at 08:33
  • @Justinas I earlier saw the same question but the problem is all the answers given there tackle the problem at each insert/update operation.If I were to do so I should end up checking in more than 1000 places.I want something like with least possible effort find a suitable solution. – Kiran Muralee Jan 17 '19 at 08:42
  • @KiranMuralee What if *fastest solution* is to check in 1000 places? Or you can convert date column to simple text... – Justinas Jan 17 '19 at 08:51
  • @Matt Ok agreed.So do you have any solution if project were upgraded to PHP 7 and MySQL 5.7 – Kiran Muralee Jan 17 '19 at 08:51
  • @Justinas for any individual check I need to know if column was DATE type if it was then I need to check if value inside the column is a valid date value, if not I should unset the date or set explicit NULL.May be these operations can be put in a common function and then itself it is overhead I would think.I am thinking of no code change in 1000 areas and do something in the connection interface library (common area where all insert/update statements pass through) or at DB level. I don't know why you marked my question as duplicate since the link provided handles at each insert/update level – Kiran Muralee Jan 17 '19 at 08:54
  • @Justinas I cannot change DATE type.Sorry if my explanation is not understood by you correctly.I need column values having DATE type to hold only valid date values (2012-02-09,2019-01-17) else NULL value. It should not conttain 0000-00-00. – Kiran Muralee Jan 17 '19 at 09:09
  • @Justinas I updated the title and content to make everyone understand the question better. – Kiran Muralee Jan 17 '19 at 09:16

1 Answers1

0

Try setting SET GLOBAL sql_mode = '';

If that doesn't help you I recommend reading up on ALLOW_INVALID_DATES Full List of SQL Modes. Hope this fixes your problem.

Red Bottle
  • 2,839
  • 4
  • 22
  • 59