CODEWITHSUNDEEP
phpmysqlintstrtotime
Davicus
Davicus

Reputation: 428

PHP strtotime saving false as MySQL negative int

I am doing a migration and dates coming as '0000-00-00 00:00:00' and running through strtotime() and returning as false are saving as -2147483648 in the new table's column, which is an INT datatype. No matter whether I set the default to 0 or NULL, it stills saves as that negative number. I want to avoid having to code exceptions for each date field (and I don't have the option to change the datatype), so is there some easy way in PHP (using one line) or MySQL to have the default be set on these "empty" dates?

Upvotes: 3

Views: 313

Answers (3)

Matthew
Matthew

Reputation: 9949

Are you looking for a default value on the column and just not passing a date to it (i.e. just setting all dates to some value if a value is not provided?)

If so this should work if you want the default to be 0 (which I am not sure you do, would be better to allow null if the dates are nonsense?):

ALTER TABLE YourTable MODIFY YourColumn Default '0';

Then ensure that you do not pass anything (or null should be ok) to the database when calling with a failed strtotime result (note strtotime will not return valid results for dates before Fri, 13 Dec 1901 20:45:54 UTC so your 0000-00-00 will fail rightly on this).

Upvotes: 0

tylermauthe
tylermauthe

Reputation: 488

Without code, it's hard to answer, but you may consider using the Ternary Operator.

ex: 

$variable_to_write_to_db = ($field == 0 || $field == NULL ? writeDefaultsToDB() : writeToDB($field))

Or something like that.

The ternary operator works like this (copied from link above):

/* most basic usage */
$var = 5;
$var_is_greater_than_two = ($var > 2 ? true : false); // returns true

good luck, have fun

Upvotes: 2

tadman
tadman

Reputation: 211690

The time 0000-00-00 is outside the range of strtotime which is bounded by 1901-12-13 through 2038-01-19. It's returning the lower bound on your broken data. This is common to nearly all UNIX time_t based functions.

My advice is to replace those dates with NULL before processing. They're not valid.

Upvotes: 1

Related Questions