Unable to convert a list field to a date/time field
5 posts by 2 authors in: Forums > CMS Builder
Last Post: April 15, 2013 (RSS)
By Codee - April 11, 2013
Client previously had years for cars on their site in a list field. They would like to convert that list field to a date/time field and use the year range function that's in it, but the system gives an error such as
There was an error changing the MySQL Column, the error was:
Data truncated for column 'me' at row 1
I realize one way would be simply to delete the older 'year' field and create a new year field with the date/time type selected...EXCEPT they already have thousands of ads in their database that use the previous list field. Is there a simple way to achieve the goal here?
By gregThomas - April 12, 2013
Hi,
The problem is that the data that is currently stored in the field must be converted to MySQL date format before you can convert the field type. Usually I use a script to convert the data first, something like this should work:
//Get All records from section with date field
$blogs = mysql_select('blog');
//Loop through records
foreach($blogs as $blog){
/*
* Assuming current field is just the year in format 07 or 2007
* Create basic date
*/
$dateString = $blog['year'].'-01-01 00:00:00';
$dateString = date('Y-m-d H:i:s', strtotime($dateString));
mysql_update('blog', $blog['num'], null, array('year' => $dateString));
echo $blog['num']." converted!<br>";
}
echo 'DONE!';
This is just example code, so you'll have to make a few changes to get it working with your site. Make sure that you backup your database before using this code!
So the mysql_select function will retrieve all of the records from the table with your date field that needs converting. Then the foreach loop passes through each record and converts the year field into a date the mysql date format. In this example code I've assumed the date is stored in the format 12 or 2012. Finally the mysql_update function is used to save the information back into the record.
Let me know if you have any questions.
Cheers
Greg
PHP Programmer - interactivetools.com
By Codee - April 12, 2013
Hi Greg,
I'm not versed in direct MySQL manipulation so I'll ask "the" question..."Where" do I run this script exactly? Do I create a .php page, upload the page and then run it via a browser? Is this something I'm to do through phpMyadmin (which scares me because I don't know what I'm doing in there)? Thanks!
By gregThomas - April 15, 2013
Hi,
You'd need to create a .php page and upload it to your server, then run it in your web browser. You only need to run it once, then you can delete it.
Cheers
Greg
PHP Programmer - interactivetools.com