Field Types & Storage

Field Types

Details: Category: 1.0; Published on Saturday, 17 July 2010 15:41; Written by Craig Phillips; Hits: 4513

EasyTable Pro supports all the standard types found in EasyTable for J! and two additional fields types to help provide better display, sorting and storage features.

It also support field options that affect the formatting of each field, you can read more about that in EasyTable Pro - Field Display Options. This article is about the way each type is stored in MySQL and some things you will want to watch out for. When you change the field type in an EasyTable you've created the underlying MySQL column type is changed as well, when this happens MySQL tries to convert any data in the columns to fit the new type - sometimes this can cause issues. The two most common issues are data truncation or data in an invalid format for the new type.

The good thing is that you can re-import the data from your CSV or TAB file quickly and easily if you experience a problem.

Data Truncation

Data truncation typically occurs when the data stored in a Text format field is too large for the intended type you have selected. As a Text field can hold literally entire books worth of text converting to any other type has the potential to truncate the date. If you are not using the text field to contain lots of text you will probably be OK, if however you change a Text field that contains more than 255 chars to an Image, Link or Email Address field type - characters after the first 255 will be truncated. ie. cut off.

Original Text Field Contents	Size	After Conversion to an Image/Link/Email Address	Size
A large piece of text may contain many hundreds of characters, as is the case with this paragraph. In this table the left column shows the original data followed by a size column that denotes the number of chars stored. The third column shows the result after conversion to one of the smaller text formats followed by the resulting size. The examples include this row which is larger that the 255 character limit of the the other text types, the next row which is just less than that limit and a final row that contains a typical type of text content that is well below the 255 character limit.	594	A large piece of text may contain many hundreds of characters, as is the case with this paragraph. In this table the left column shows the original data followed by a size column that denotes the number of chars stored. The third column shows the result a	255
Now is the winter of our discontent. Whether tis nobler in the mind to suffer the slings and arrows of outrageous fortune, Or to take arms against <a href="http://www.google.com.au/search?q=iphone+4+death+grip">a death grip</a>, And by opposing end it?	252	Now is the winter of our discontent. Whether tis nobler in the mind to suffer the slings and arrows of outrageous fortune, Or to take arms against <a href="http://www.google.com.au/search?q=iphone+4+death+grip">a death grip</a>, And by opposing end it?	252
george_washington.jpg	21	george_washington.jpg	21

Invalid Format

If you are converting a Text, Image, Link or Email Address field type to a Number or Date and the original contents of the field are not in a suitable format the conversion will fail, resulting in the loss of data.

For conversion to a Number we recommend you read the MySQL discussion of Numeric Types if you experience conversion problems.

For conversion to Date we similarly recommend you read the MySQL discussion of Date Types.

Original Text Field Contents	New Type	After Conversion
A piece of text may contain a date, but it may not convert if MySQL doesn't understand it.	DATE	*(Yes - it's empty, MySQL couldn't find a date value it recognised.)
2009/12/24	DATE	2009-12-24
george_washington.jpg	NUMBER	0
$29.95	NUMBER	29.95

Type Mapping

The following table show's how each of the EasyTable Pro field types maps to MySQL Types

EasyTable Type	MySQL type	Typical Data	Notes
Text	TEXT	Now is the winter of our discontent. Whether tis nobler in the mind to suffer the slings and arrows of outrageous fortune, Or to take arms against <a href="http://www.google.com.au/search?q=iphone+4+death+grip">a death grip</a>, And by opposing end it?	The largest storage type TEXT fields can contain very large amounts of data. Converting to TEXT is generally issue free. Converting *from* TEXT to any other format runs the risk of data being truncated or lost completely if it's not in a format that useable by the new type.
Image, Link or Email Address	VARCHAR(256)	george_washington.jpg
Number	FLOAT	1,234,456.7890	A FLOAT type allows for real world decimal style numbers with varying precision. Most number formats in CSV & TAB files can be imported without issue.
Date	DATE	2010-01-31	Date fields in your import file should be in a format MySQL recognises such as YYYY-MM-DD.
Date	DATE	MySQL retrieves and displays `DATE` values in `'YYYY-MM-DD'` format. The supported range is `'1000-01-01'` to `'9999-12-31'`. When creating your CSV or TAB file make sure that dates are exported with a four digit year YYYY to prevent ambiguity. Dates containing two-digit year values, YY, are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules: Year values in the range 00-69 are converted to 2000-2069. Year values in the range 70-99 are converted to 1970-1999.