Query's results manipulation in SQL or PHP?

Question

I wrote this query:

SELECT p.category, r.id, r.code, u.hash_name, r.created_at, r.updated_at FROM uploads AS u
JOIN refrigerators AS r ON u.id=r.image
JOIN products_refrigerators AS pr ON pr.refrigerator_id=r.id
JOIN products p ON pr.product_id = p.id

And this is the result:

Array
(
    [0] => stdClass Object
        (
            [category] => lamps
            [id] => 1
            [code] => canopy
            [path] => public/uploads/AAmAnnC2Yoij9QblcybTJefE5vTQrNZsfDCWqOPt.jpeg
            [created_at] => 2018-02-21 11:15:06
            [updated_at] => 2018-02-21 11:15:06
        )

    [1] => stdClass Object
        (
            [category] => panels
            [id] => 1
            [code] => canopy
            [path] => public/uploads/AAmAnnC2Yoij9QblcybTJefE5vTQrNZsfDCWqOPt.jpeg
            [created_at] => 2018-02-21 11:15:06
            [updated_at] => 2018-02-21 11:15:06
        )

    [2] => stdClass Object
        (
            [category] => lamps
            [id] => 2
            [code] => canopy2
            [path] => public/uploads/nqDT4gEXbF0opPmEpa1cPo5iPSulRoesYcgHiOA3.jpeg
            [created_at] => 2018-02-21 11:17:44
            [updated_at] => 2018-02-21 11:17:44
        )

    [3] => stdClass Object
        (
            [category] => lamps
            [id] => 3
            [code] => QWE
            [path] => public/uploads/0TSrz6j3JJH17XgEs2161mOh8aYgDikujdYwrCWR.jpeg
            [created_at] => 2018-02-21 11:18:13
            [updated_at] => 2018-02-21 11:18:13
        )

    [4] => stdClass Object
        (
            [category] => panels
            [id] => 3
            [code] => QWE
            [path] => public/uploads/0TSrz6j3JJH17XgEs2161mOh8aYgDikujdYwrCWR.jpeg
            [created_at] => 2018-02-21 11:18:13
            [updated_at] => 2018-02-21 11:18:13
        )

)

My question is: is there a way to get a result like this (= transform the category property into an array which groups all the related categories, if more than one exist)

Array
(
    [0] => stdClass Object
        (
            [category] => Array 
                          (
                              [0] => lamps
                              [1] => panels
                          )
            [id] => 1
            [code] => canopy
            [path] => public/uploads/AAmAnnC2Yoij9QblcybTJefE5vTQrNZsfDCWqOPt.jpeg
            [created_at] => 2018-02-21 11:15:06
            [updated_at] => 2018-02-21 11:15:06
        )

    [1] => stdClass Object
        (
            [category] => Array 
                          (
                              [0] => lamps
                          )
            [id] => 2
            [code] => canopy2
            [path] => public/uploads/nqDT4gEXbF0opPmEpa1cPo5iPSulRoesYcgHiOA3.jpeg
            [created_at] => 2018-02-21 11:17:44
            [updated_at] => 2018-02-21 11:17:44
        )

    [2] => stdClass Object
        (
            [category] => Array 
                          (
                              [0] => lamps
                              [1] => panels
                          )
            [id] => 3
            [code] => QWE
            [path] => public/uploads/0TSrz6j3JJH17XgEs2161mOh8aYgDikujdYwrCWR.jpeg
            [created_at] => 2018-02-21 11:18:13
            [updated_at] => 2018-02-21 11:18:13
        )

)

directly with MySQL or the only way is to manipulate the results (in PHP, in my case)?

Probably the answer is "you must use PHP" but I'm not a MySQL expert, that's why the question.

You might be able to do a [`GROUP_CONCAT()`](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) with a `GROUP BY p.id`. — Patrick Q, Feb 21 '18 at 18:48
@PatrickQ Amazing, `GROUP_CONCAT(p.category)` with `GROUP BY r.id` did exactly what I wanted. It doesn't create an array but this I guess is definitely my duty in PHP. Thanks Patrick! — Brigo, Feb 21 '18 at 18:57
Yeah, if you want an actual PHP array, you'd need to run [explode()](http://php.net/manual/en/function.explode.php) on the result. — Patrick Q, Feb 21 '18 at 18:58

score 1 · Accepted Answer · answered Feb 21 '18 at 18:59

1

you should concatenate your category column and then group it by id, like Patrick mentioned in comments.

SELECT GROUP_CONCAT(p.category), r.id ... GROUP BY r.id

also you can use JSON_ARRAY or JSON_OBJECT functions to achieve similar result, but do not forget to group them

answered Feb 21 '18 at 18:59

devnull Ψ

3,779
7
26
43

`JSON_ARRAY` would actually be awesome in this case, but unfortunately the server is running MySQL 5.6 and not 5.7. Thanks for having pointed this function out! – Brigo Feb 23 '18 at 09:20
1

yes, I forgot to mention mysql version. btw, if you gonna use these functions in future, note that `GROUP_CONCAT`has limited string length, so you may have case when not the whole data fit in your column, I faced this problem, take a look, it may save your time in future https://stackoverflow.com/questions/48956278/mysql-doesnt-return-proper-json – devnull Ψ Feb 24 '18 at 15:32

Query's results manipulation in SQL or PHP?

1 Answers1