mysql multidimensional array

[Destramic]

hey guys, i have a bit of a strange question and dont know if what im asking can actually be done with mysql but here goes.

 

basically i have a table like so:

 

items

--------------------

item_id

category

sub_category

sub_sub_category

name

price

---------------------

 

now what im after is a result like so:

 

category           sub_cat             sub_sub_cat

[consoles] => [accessories] => [headsets] => [0] => ('name' => 'item1',

                                                                                   'price' => '10.00')

 

now ive seen a query which converts to json (but didnt work)

but would be great if its possible to do what i want it to do

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

any advise would be great

 

thank you

 

[Barand]

Don't try building the json string in the query. Store query results in the array structure you want then json_encode that.

[Destramic]

ok thanks

 

but is it possible to get my results from the database like so:

 

category           sub_cat             sub_sub_cat

[consoles] => [accessories] => [headsets] => [0] => ('name' => 'item1',

                                                                                               'price' => '10.00')

 

 

[Barand]

$sql = "SELECT item_id

        , category

        , sub_category

        , sub_sub_category

        , name

        , price

        FROM items

        ORDER BY category, sub_category, sub_sub_category";

$res = $db->query($sql);

$data = array();

while (list($id, $cat, $subcat, $subsubcat, $name, $price) = $res->fetch_row()) {

    $data [$cat][$subcat][$subsubcat][$id] = ['name'=>$name, 'price'=>$price];

}