Store exploded GROUP_CONCAT columns to an array without nesting

Viewed 34

I'm working with a discography database and in order to make some instances of displaying data easier, I have a few columns in my SQL query that use GROUP_CONCAT().

SELECT
     aName as Album,
     atSong as Track_Number,
     tName as Track,
     GROUP_CONCAT(subTLV SEPARATOR ', ') as Option,
     GROUP_CONCAT(subTranslator SEPARATOR ', ') as Translator,
     GROUP_CONCAT(subLanguage SEPARATOR ', ') as Subtitles,
     GROUP_CONCAT(subURL SEPARATOR ', ') as SRT
FROM ALBUM
     LEFT JOIN ALBUM_TRACK USING (aID)
     LEFT JOIN TRACK_SUBTITLES USING (tCode)
WHERE aName = 'Album'
GROUP BY tName
ORDER BY subLanguage, subTLV, atSong

I am using a WHILE loop to retrieve results and store them in separate arrays and have two ways of doing it currently:

while ($row = mysqli_fetch_array($result)) {
     // method 1
     $GC_SUBS[$i_SUBS] = explode(', ', $row['Subtitles']);
     $i_SUBS++;

     // method 2
     $a_SUBS[$i_SUBS] = $row['Subtitles'];
     $i_SUBS++;
}

For an album that has 4 tracks, the resulting arrays look like this:

// method 1
array(4) {
  [0]=>
  array(1) {
    [0]=>
    string(7) "English"
  }
  [1]=>
  array(2) {
    [0]=>
    string(7) "English"
    [1]=>
    string(6) "Korean"
  }
  [2]=>
  array(1) {
    [0]=>
    string(7) "English"
  }
  [3]=>
  array(1) {
    [0]=>
    string(7) "English"
  }
}
// method 2
array(4) {
  [0]=>
  string(7) "English"
  [1]=>
  string(15) "English, Korean"
  [2]=>
  string(7) "English"
  [3]=>
  string(7) "English"
}

Is there a way for me to both explode the strings and store it into an array that will result in only one level? This is what I'm trying to achieve:

array(5) {
  [0]=>
  string(7) "English"
  [1]=>
  string(7) "English"
  [2]=>
  string(6) "Korean"
  [3]=>
  string(7) "English"
  [4]=>
  string(7) "English"
}
1 Answers

Initialize $GC_SUBS outside the loop as an array and the merge the output of explode with $GC_SUBS like $GC_SUBS = array_merge($GC_SUBS, explode(', ', $row['Subtitles']);

Related