Php – Split Column with MySQL

mysqlphpsql

I recently took over a project that has a pretty dirty database… The table "users" has a "phone" column… The problem is that the column holds multiple phone numbers separated by "/". I would like to put all of these in separate columns (phone 1, phone2, phone3 etc) But have no idea what the best way to do it is. Is it possible with MySQL alone? Should I write some sort of PHP script? Any hints would be greatly appreciated…

Mike

Best Solution

If you are unsure the number of phone number you will need per record, you might want to have a phone numbers table. It may be easier for you to run a php script to update the database like so (I'm assuming you don't sanitize your phone numbers to a specific format and that you use INNODB):

Create the phone number's table:

CREATE TABLE `user_phone` (
  `userid` int(10) unsigned NOT NULL,
  `phone` char(15) NOT NULL,
  PRIMARY KEY  (`userid`,`phone`),
  CONSTRAINT `fk_user_phone_userid` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then write a php script to split the existing field data and create inserts into the new table.

EDIT: As mentioned below in my comment, you could have a backwards compatable view that would still have the phone numbers concat together until you can update everything:

CREATE VIEW `old_table` AS
    select `u`.*, group_concat(`up`.`phone` separator '/') AS `phone`
    from `user_phone` `up`
        left join `users` `u` on(`up`.`userid` = `u`.`userid`)
    group by `u`.`userid`
Related Question