How to parse this LONGTEXT string field in MySQL

Question

I need to extract the following fields into a new table, any ideas if I can do this exclusively with a query or if I need to use PHP as well.

CURRENT TABLE STRUCTURE

USERID USEREXPERINCE
1      a:4:{i:0;s:20:"business development";i:1;s:6:"design";i:2;s:9:"marketing";i:3;s:15:"press relations";}

REQUIRED TABLE STRUCTURE

USERID             USEREXPERINCE
1                  business development
1                  design
1                  marketing
1                  press relations
2                  web development
2                  design
3                  marketing
3                  business development

Thanks

Jonathan

Answer 1

You need to use PHP - the 'LONGTEXT' data is in fact a serialized PHP array.

Execute the following to see what I mean:

<?php
print_r(unserialize('a:4:{i:0;s:20:"business development";i:1;s:6:"design";i:2;s:9:"marketing";i:3;s:15:"press relations";}'));
?>

As such, the easiest thing to do would be to read each row from the database, unserialize the data and insert it into a new table with the required fields. (I'm guessing you need to search on these, hence the need to store them as dedicated fields.)

That said, the serialized string you provided only appears to be storing IDs -> Field names (rather than any values), so I'm not sure what's going on there.

Answer 2

I would use PHP for this, simply because it is easier to call unserialize() and generate new INSERT statements than to parse the string in a MySQL procedure (though that could also be done). Also beware if your USERID column is currently a primary key, since it cannot be with the new structure.