DCR DCR - 15 days ago 6
MySQL Question

pulling data out of mysql creating array with array structure

I have a MySql database with three columns: skp, date and usr. For each skp and date pair there are several usr(s). Is there a query that will pull the data out with the following array structure:

array[i]['skp']
array[i]['date']
array[i]['usr'][j]


I'm wondering if somehow using a group by would work?

Answer

Use GROUP_CONCAT.

SELECT skp, date, GROUP_CONCAT('usr') AS usr
FROM yourTable
GROUP BY skp, date

Then when retrieving, you can split the usr column into an array.

$array = ();
while ($row = mysqli_fetch_assoc($result)) {
    $row['usr'] = explode(',', $row['usr']);
    $array[] = $row;
}