1

I want to modify table in my production server by extracting some column and put it into another table, the problem is the old column has multiple value separated by commas and in new table will be put in multiple records by query. i know the way like INSERT INTO user_emails(user_id, email) SELECT id, email FROM users but it would copy entire content of field.

table users (old)
-----------------------------------------------------
id  name  emails (removed)
-----------------------------------------------------
1   A     [email protected], [email protected]
2   B     [email protected], [email protected], [email protected]

table user_emails (new table)
---------------------------------
id  user_id  email
---------------------------------
1   1        [email protected]
2   1        [email protected]
3   2        [email protected]
4   2        [email protected]
5   2        [email protected]

Any solution? Thank you

Angga Ari Wijaya
  • 1,759
  • 1
  • 15
  • 31
  • 1
    This should convince you why [you should not store comma-separated lists of values](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574), if you actually want SQL to work with the individual elements of the lists. – Bill Karwin Nov 01 '17 at 06:38
  • I know, it's a bad practice, above is example data, before we store unimportant data such a message description but now we need in proper table master-detail so we can easily to manage that. – Angga Ari Wijaya Nov 01 '17 at 07:14
  • @billkarwin it seems like they’re trying to fix that issue – Strawberry Nov 01 '17 at 07:57

1 Answers1

1

you should get fetch data from DB and store into array and then use explode() function to split the data

<?php
   $data=explode("," , $allEmails);
?>

now $data is convert into array like this

Array ( [0] => [email protected] [1] => [email protected] [2] => [email protected] )

finally you can insert into new table using insert query

Bilal Ahmed
  • 4,005
  • 3
  • 22
  • 42