sorting serialized data in GROUP_CONCAT() using php

43
January 20, 2019, at 8:00 PM

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?

Answer 1

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.

READ ALSO
What is the fastest way to send data from android app to mysql database? [on hold]

What is the fastest way to send data from android app to mysql database? [on hold]

I am a web developer and I have no experience in Android developingBut i have started developing a android app that gets every sms message that is received and i need it to send those to MySQL database so after i can access the data from my php website

57
Static Application Security Testing for JavaScript [on hold]

Static Application Security Testing for JavaScript [on hold]

can anybody recommend open-source SAST Tools for JavaScript and NodeJSI'm currently using NodeJSScan which is OK but has a very limited ruleset and SonarQube with the SonarJS plugin which is - regarding security checks - not good

67
How to use different match in populate base on the found value?

How to use different match in populate base on the found value?

I want to use different match condition in populate base on the record's one field valueI have tried use $where in find(), but it cannot find in populate field

48
Building a Docker image for a Node.js app in GitLab CI

Building a Docker image for a Node.js app in GitLab CI

I'm working on a Nodejs application for which my current Dockerfile looks like this:

79