PHP & MYSQL Database

I have a HUGE database where users type in the date such as 12/25/2015 in the field & submit it via PHP code. This data gets dumped into a MYSQL database. The MYSQL field it gets dumped into is a VARCHAR. Is there anyway I can edit the filed in the MYSQL database to a date field without corrupting the data. I have a test database where I have used ALTER TABLE supervision CHANGE Test_Date Test_Date DATE NULL DEFAULT ‘0000-00-00’ & it does what I want it to do but the existing data all turns to 0000-00-00. Thanks

Since the dates appearently use the custom format “M/D/Y”, MySQL doesn’t recognize them. So you can’t just change the column type. It’s also rather likely that you have broken dates in your database, because a [tt]VARCHAR[/tt] obviously doesn’t provide any data integrity whatsoever. You need to account for that as well.

So I’d take the safe approach: Make an additional(!) [tt]DATE[/tt] column and write a script which iterates over the rows one by one and tries to parse the date from the [tt]VARCHAR[/tt] column. If this succeeds, you can insert the date into the [tt]DATE[/tt] column, otherwise you should display an error message so that the problem can be fixed manually.

[php]<?php

const INPUT_DATE_FORMAT = ‘m/d/Y’;
const MYSQL_DATE_FORMAT = ‘Y-m-d’;

$timeZone = new DateTimeZone(‘UTC’);

// test input
$inputDate = ‘12/25/2015’;

// try to parse the input
$date = DateTime::createFromFormat(INPUT_DATE_FORMAT, $inputDate, $timeZone);

if ($date)
{
/*
* You should make additional plausibility checks to detect garbage dates and
* finally update the DATE column with $date->format(MYSQL_DATE_FORMAT).
*/
}
else
{
// display an error message
echo “Erroneous date $inputDate for ID …”;
}[/php]

After running this you can change the column type.

[php]UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, ‘%m/%d/%Y’))
WHERE DATE(STR_TO_DATE(date_field, ‘%m/%d/%Y’)) <> ‘0000-00-00’;[/php]

Well, after having verified the data is ok they can just drop the old column

Sponsor our Newsletter | Privacy Policy | Terms of Service