Solved

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

Posted on 2016-09-11
9
34 Views
Last Modified: 2016-10-01
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.

Eddie
0
Comment
Question by:Edward Guereque
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 125 total points (awarded by participants)
ID: 41793622
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.
1
 
LVL 77

Assisted Solution

by:arnold
arnold earned 125 total points (awarded by participants)
ID: 41793637
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.
1
 

Author Comment

by:Edward Guereque
ID: 41793774
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.
0
 

Author Comment

by:Edward Guereque
ID: 41793778
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.
1
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 52

Accepted Solution

by:
Julian Hansen earned 250 total points (awarded by participants)
ID: 41793805
Do you mean something like this
// ASSUMES mysqli CONNECT $db

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

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

   // CREATE THE INSERT BATCH QUERY 
   $rquery = "INSERT INTO reasons (submission_id, reason) VALUES"

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

   // REMOVE TRAILING ,
   trim($rquery, ',');

   // SEND TO DB
   $db->query($rquery);
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41794009
@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.
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html
1
 

Author Comment

by:Edward Guereque
ID: 41794581
@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.
0
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 41794693
@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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can i modify my File download link ? 6 49
Change text to radio button and calendar form 2 34
Session timeout 5 14
Why my select dropdown does not work? 8 26
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now