Split Column with MySQL

Question

Hello,

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

Answer 1

writing a PHP script is probably your best bet.

Is there a finite number of possible phone numbers? If so, I would just make new columns for them. If not, I would make a new phone number table that contains a user id and relate them that way.

after that, just query for the data, and use $phone_array = explode("/",$phone_data);

then start inserting them back into the database.

additionally, you can do it with a series of queries, using the substring_index function.

Answer 2

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`

Answer 3

I'd definately end up writting that as a script.

I like the idea of putting all the phone numbers into a new table entirely, but if there will be issues with performance or implementation issues to doing that then, for sure, leave the fields as phone1, phone2 etc.

Either solution would look something like this..depending on how you access the DB and if you need to eek out all the performance possible.

I'd probably end up with something like:

$query = "SELECT id, phone FROM users";
$result = $db->query($query);
while ($row = $result->fetch_assoc()) {
  $phones = array();
  $phones = explode('/', $row['phone']);
  $i = 1;
  foreach($phones as $p) {
    if (strlen($p) >= 7) {
      $row['phone'.$i] = $p;
      $i++;
    }
  }
  $upquery = "UPDATE users SET phone1='{$row['phone1}', phone2='{$row['phone2}', ...
              WHERE id={$row['id'];";
  $result = $db->query($query);
}

I have the string length test in place to prevent odd fragments from being saved as phone numbers. You could also use a little regex magic to pretty up each phone number before saving it.

Ways to make it faster would be to run multiple updates in the same query. If the script times out on you then you could restrict the number of user rows that are affected by each execution and run it multiple times.