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
//from
echo $this->Form->input('comment');
//to
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

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

PHPSECLIB3 Client Algorithms

Recently our EDI supplier notified that they were changing their SFTP encryption and data integrity algorithms I use...