Split comma separated values from a table column into rows using php PHP

Would anyone have an idea or point me in a direction on how I would be able to split a comma separated field into it's own rows on another table? I'm currently using php and mysql. Greatly appreciated. Thanks.

Edward GuerequeAsked:
Who is Participating?
Julian HansenConnect With a Mentor Commented:
Do you mean something like this
// ASSUMES mysqli CONNECT $db

// GET RECORDS FROM submissions TABLE 
$query = "SELECT * FROM submissions";
$result = $db->query($query);

while($row = $result->fetch_object()) {
   // explode REASON INTO reasons
   $reasons = explode(',', $row->reasons);

   $rquery = "INSERT INTO reasons (submission_id, reason) VALUES"

   foreach($reasons as $r) {
      $rquery .= "('{$row->id}','{$r}'),";

   trim($rquery, ',');

   // SEND TO DB

Open in new window

Ray PaseurConnect With a Mentor Commented:
The most important and useful thing we can have for a question like this is a test data set and an understanding of where you are on the novice-expert continuum in the relevant technologies.  Armed with a little more information, I am sure we can get you a tested-and-working code sample!

PHP has the explode() function that might be helpful, but it's got some data-dependent quirks, so we might do well to see the actual data we are dealing with.
arnoldConnect With a Mentor Commented:
You would pull he data into php, and split it within php. It seems from your question you want to convert the data within MySQL.

The data sample RAy mentioned could help.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Edward GuerequeAuthor Commented:
Yes,  here's a better example.  I have submissions table in a database.  In the submissions table there is a field called invalid reasons.  Some submissions have one invalid reason and some have three or four and they are comma delimited in that field. I am wanting to move the invalid reasons field into rows in its own table.  I'm thinking I'm needing to keep the same id for the multiple invalid reasons as well.  Hope this isn't too confusing.  

Oh, the database is MySQL and code I'm using is PHP.  Thanks.
Edward GuerequeAuthor Commented:
Oh yes, I'm thinking I need select from the database, iterated through the rows and explode (explode function) the rows that have comma delimited values then insert into it's own table.
Ray PaseurCommented:
@Julian: I think you're on the right track, but there probably needs to be a many-to-many relationship between submissions  and reasons, implying some kind of a junction table or similar structure.

@Edward: Please don't keep us guessing.  A description  of your data is never as good as seeing the actual  data.  It's like showing a hungry man a picture of a sandwich!  Please post the test data.  Thanks.

If you're new to PHP and want to learn the language, this article can help you get a good footing.
Edward GuerequeAuthor Commented:
@Ray:  Thank you for sharing your Post test data link and the PHP articles as well.  I will be posting test data shortly. I'm new to Experts Exchange and I was wondering how people are sharing test data.  Or I can attach a csv file a well.  Thanks.

@Julian:  Thank you for your input.  I'm taking a look.
Julian HansenCommented:
@Julian: I think you're on the right track, but there probably needs to be a many-to-many relationship between submissions  and reasons, implying some kind of a junction table or similar structure.
If we are maintaining reasons as an entity on its own - but that would imply some sort of structured selection of reasons. If people are freeforming it then the link to reasons does not need to go through a linking table.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.