Combining Multiple Table Columns in MySQL
Question
How to combine address1, address2, address3 and address4 in the following table to a single column such as address?
CREATE TABLE user ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR( 255 ) NOT NULL , `email` VARCHAR( 255 ) NOT NULL , `address1` VARCHAR( 100 ) NOT NULL , `address2` VARCHAR( 100 ) NOT NULL , `address3` VARCHAR( 100 ) NOT NULL , `address4` VARCHAR( 100 ) NOT NULL , `postcode` VARCHAR( 6 ) NOT NULL , `state` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM;
Answer
First of all, let alter the table and add columns address and set it as TEXT.
ALTER TABLE `user` ADD `address` TEXT NOT NULL AFTER `email`;
Next on the list, is to migrate existing data from address1, address2, address3 and address4 to address. For this you can either do it with either any programming language including PHP, Ruby-on-Rails but it’s always easier to do it directly with MySQL query, here we will be using CONCAT() function to concat all four columns.
UPDATE `user` SET `address` = CONCAT(`address1`, "\n", `address2`, "\n", `address3`, "\n", `address4`);
Nice tutorial, very helpful.
“address” also can be use when query the table without adding another column.
for example,
———
SELECT `id`,`name`,`email,`postcode`,`state`, CONCAT(`address1`, “\n”, `address2`, “\n”, `address3`, “\n”, `address4`) as `address`
———
But the limitation is still there, to edit the value of address we still have to end up using 4 input field to cater address alone while using textarea will cause issue if let say the user key-in more than 4 line.
Not what I was looking for. Is there a way to combine rows from two columns into one col? For example, 10 rows from props.title and 5 rows from tickets.title to make a title column with 15 rows in?