Brtdku

I have a table with these columns:

contest_id, exhibition_id, username, files

The column files contain pictures which I serialized while placing in the database because they are multiple files.
The column files looks like this

"a:3:{i:0;s:9:"10210.PNG";i:1;s:9:"99073.PNG";i:2;s:9:"89321.PNG";}"

I performed this SQL statement using PHP

$a = mysqli_query($this->con, "SELECT username,  GROUP_CONCAT(files) files from my_exhibition_contests WHERE exhibition_id='$id'");

and went ahead to write this in PHP

$s=$a->fetch_all(MYSQLI_ASSOC); 

foreach ($s as &$row) {

    $row['files'] = unserialize($row['files']); 

}

echo json_encode($s);

Then I collected it at the front end using angularjs.
I used the GROUP_CONCAT(files) so as group it by username and put all the files with that username in a single row.
The problem is that using foreach ($s as &$row) defeated the purpose of GROUP_CONCAT(files), it only brought one of the result as if I used GROUP BY, but on removing the foreach ($s as &$row), it works the way I want it(i.e it packed all rows in files with that particular username in a single column with the alias name files) but the values in each index of files are serialized, in string format.
How can I unserialize each index of in the files array.
Let me give this example, let assume the first row in the table is like this

contest_id: 1 exhibition_id: 5 username: John files: "a:3:{i:0;s:9:"10210.PNG";i:1;s:9:"99073.PNG";i:2;s:9:"89321.PNG";}"

and the second row is like this

contest_id: 2 exhibition_id: 5 username John: files: "a:3:{i:0;s:9:"33937.PNG";i:1;s:9:"26831.PNG";i:2;s:8:"6316.PNG";}"

When I removed the foreach I got this

username: John files: "a:3:{i:0;s:9:"10210.PNG";i:1;s:9:"99073.PNG";i:2;s:9:"89321.PNG";},a:3:{i:0;s:9:"33937.PNG";i:1;s:9:"26831.PNG";i:2;s:8:"6316.PNG";}"

It puts everything in a single column files which is what I want but the issue is that it is serialized in string format.

When I put the foreach lines, I got this

username: John files : ["10210.PNG", "99073.PNG", "89321.PNG"]

This is in an array format, exactly what I want but it only brought the first row, how can I go about it so that it will be in array format, yet bring all the rows?

Your issue is that you need to break apart the GROUP_CONCAT'ed file lists before you unserialize them, and then merge all the results. This should work:

$s=$a->fetch_all(MYSQLI_ASSOC);

foreach ($s as &$row) { 

    $files = array();

    foreach (preg_split('/}(,|$)/', $row['files'], -1, PREG_SPLIT_NO_EMPTY) as $filelist) {

        $files = array_merge($files, unserialize($filelist . '}')); 

    } 

    $row['files'] = $files;

}

echo json_encode($s);

Demo of splitting code on 3v4l.org

Note

We have to use preg_split to split the data because there are also commas in the serialized data. If you change your query to use a different separator that won't be present in one of the filenames e.g.

$a = mysqli_query($this->con, "SELECT username,  GROUP_CONCAT(files SEPARATOR '#') files from my_exhibition_contests WHERE exhibition_id='$id'");

then you could replace the preg_split with

explode('#', $row['files'])

This would also remove the need to add back the } split character to $filelist before unserializing.