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?

+1 For the initiative to properly normalize this data.

Michael Berkowski
– Michael Berkowski

2012-04-29 13:21:46 +00:00
Commented Apr 29, 2012 at 13:21 — Michael Berkowski
– Michael Berkowski, Commented Apr 29, 2012 at 13:21

Michael Berkowski · Accepted Answer · 2012-04-29 13:20:53Z

2

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.

answered Apr 29, 2012 at 13:20

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1286499 Over a year ago

Thank you @Michael. Looks like FIND_IN_SET() fit to my need at the moment.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related