CakePHP 2.x Reducing Database Column Sizes by changing from TEXT to VARCHAR Column Types

Written by James McDonald

October 18, 2019

I was using a TEXT database field for some text settings and found that it uses 64kb of data

SELECT name, char_length(comment)/1024 FROM settings;

Checking the field size with the above query I found that the largest content was 0.6660 kb. So I had specified a massively too large column type for the comment column

To check your field sizes you can run this query

select column_name, data_type, character_maximum_length, table_schema
  from information_schema.columns  
 where table_name = 'YourTableName' AND table_schema = 'YourDBname'

So after this I changed from the TEXT column type back to VARCHAR as follows

ALTER TABLE `MyDBName`.`settings` 
CHANGE COLUMN `MyColumnName` `MyColumnName` VARCHAR(1000) NOT NULL;

This then created a problem in the CakePHP view that the input field was now automatically outputting an input field of type text and I wanted multi-line input.

So had to change my view code slighty

// view
echo $this->Form->input('comment');
echo $this->Form->input('comment', ['type' => 'textarea']);

This stops CakePHP automagically setting the input for a VARCHAR field to type=”text” and you can then enter multi-line content to your hearts content


Submit a Comment

Your email address will not be published.

You May Also Like…

PHP Iterators

Just came across a Youtube talk "Iterators in PHP" by Jake Smith published in 2014 that steps through the many...

PHP array_map Multiple Arrays

array_map can take multiple arrays. I like how it starts mapping through them starting at the first element of each...