0

I hope this isn't a duplicate question, I have found many similar but none that match my requirements. I tried to get a solution with Splitting mysql value into unknown number of parts but I think maybe I asked the wrong question - I specified not wanting to use php with that question. It also is close to Can you split/explode a field in a MySQL query? but again, that doesn't quite define it. I installed common_schema which looks like it will be a powerful tool but I can't get it return the result I wish. I have tried foreach in conjunction with explode but I can't get all the data in the same array with it. Long preamble but here is the problem. I have a DB with the following table.

id categories

100 |88|

102 |88|243|

442 |228|243|228|239|228|

I'm restructing this to put it into OpenCart.

So need it like this or in an array that I can use to create this

ID category

100 88

102 88

102 243

442 228

442 243

442 228

etc

I don't have a problem getting it into and out of the database, just amending the data to tie up the ID to each category however many there may be in the field. I have spent about 2 days trying to find a solution and I'm sure it's very basic. The closest I've got is with foreach

but ended up with an array 

array ('ID' => 442,
       'Category' => array (0 => 228, 1 => 243, 2 => 228 etc)
Community
  • 1
  • 1
TimP
  • 694
  • 5
  • 19
  • 1
    Looks like you need to normalize your data. – Ben Fortune Nov 06 '13 at 15:53
  • @ Ben Fortune - are you referring to the fact that `id 422` has `category 228` in it 3 times? I didn't notice that, but that is actual data from the DB - This has added a new problem. – TimP Nov 06 '13 at 19:08

1 Answers1

1

Depending on how you're retrieving the query result, you could do something like this:

foreach($result as $row) { // iterate over each row in the query result

    $categories = array_filter(explode('|', $row['categories']));

    foreach($categories as $category) {
        // insert row into new table with id $row['id'] and category $category
    }
}
George Brighton
  • 5,131
  • 9
  • 27
  • 36
  • I'm calling it in as an array using SELECT id, categories FROM table. I need to read up on the range of array_... tools in php. I looked at array_walk and array_map but neither did the job I wanted. Thx. – TimP Nov 06 '13 at 15:46
  • @TimP Ok. If you're using PDO, `$result` would simply be the array returned by `fetchAll()`. – George Brighton Nov 06 '13 at 15:50
  • Could I enter it into the db on each iteration.? Avoiding reassignation? I know that would be massively inefficient in a production environment. But this is just a one time data conversion. – TimP Nov 06 '13 at 15:50
  • That was a response to a comment that has now disappeared. You have now commented in the code what I meant - insert row on each iteration. – TimP Nov 06 '13 at 15:56