How do I create a MySQL statement to split array data to new table format

Question

I have old table which the data stored as

tbl_id  tbl_data
--------------------
1       1,2,3,4,5,6
2       3,4,5

Now I want convert these to many-to-many relationship table.

user_id user_option
-------------------
1       1  
1       2
1       3
...

Basically I insert the data in php

$old = explode(",", $data['tbl_data']);
$query = $db->query("SELECT tbl_id, tbl_data FROM old_table"); 
  while($fetch = $db->fetch($query)) {
    $db->query("INSERT INTO new_table SET .....");
  }

May I know, there have a way to get this done with single MySQL statement?

Answer 1

No, you are best off to do it in PHP code. Though MySQL does have a way to locate values via FIND_IN_SET(), it doesn't have an equivalent method to explode() to easily separate them. It would be possible to write a lot of ugly looping and string manipulation in a MySQL procedure, but it's much easier to do in PHP code.

There are some suggestions for handling string splitting in the comments beneath the MySQL string function docs but they don't really address looping inserts to normalize out a column. It really is easier to just handle in a scripting lanugage like PHP.

But it is good that you are sorting this out and properly normalizing the column.