Geek asks and answers

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

I have an old table whose data is stored as

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

Now I want to convert them to a many-to-many relationship table.

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

I mainly insert 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 .....");
  }

Can I find out if there is a way to do this with a single MySQL statement?

+3
source share
1 answer

No, your best bet is to do this in PHP code. Although MySQL has a way to find values ​​through FIND_IN_SET(), it does not have an equivalent method for explode()to easily separate them. You could write a lot of ugly loops and string manipulations in a MySQL procedure, but it is much easier to do in PHP code.

MySQL string function docs, . lanugage, PHP.

, .

+2

More articles:


All Articles