display mysql data as array

Question

I have this type of data in mysql table

+------+------+-------+----------+------------------+
|   id | cat  | mode  | category | content          |
+------+------+-------+----------+------------------+
|    1 | desc | mode1 | cat1     | 255 char content |
|    2 | type | mode1 | cat1     | 32 char content  |
|    3 | type | mode1 | cat1     | 32 char content  |
|    4 | type | mode1 | cat1     | 32 char content  |
|    5 | desc | mode2 | cat2     | 255 char content |
|    6 | type | mode2 | cat2     | 32 char content  |
|    7 | type | mode2 | cat2     | 32 char content  |
|    8 | type | mode2 | cat2     | 32 char content  |
|    9 | type | mode2 | cat2     | 32 char content  |
|   10 | desc | mode3 | cat3     | 255 char content |
|   11 | type | mode3 | cat3     | 32 char content  |
|   12 | type | mode3 | cat3     | 32 char content  |
|   13 | type | mode3 | cat3     | 32 char content  |
|   14 | type | mode3 | cat3     | 32 char content  |
|   15 | type | mode3 | cat3     | 32 char content  |
+------+------+-------+----------+------------------+

how to display it like

Array
(
    [modes] => Array
    (
        [mode1] => Array
        (
            [desc] => Array
            (
                [0] => 255 char content
            )
            [type] => Array
            (
                [0] => 32 char content
                [1] => 32 char content
                [2] => 32 char content
            )
        )
        [mode2] => Array
        (
            [desc] => Array
            (
                [0] => 255 char content
            )
            [type] => Array
            (
                [0] => 32 char content
                [1] => 32 char content
                [2] => 32 char content
                [3] => 32 char content
            )
        )
        [mode3] => Array
        (
            [desc] => Array
            (
                [0] => 255 char content
            )
            [type] => Array
            (
                [0] => 32 char content
                [1] => 32 char content
                [2] => 32 char content
                [3] => 32 char content
                [4] => 32 char content
            )
        )
    )
)

I have tried this

$_link      = mysqli_connect("localhost","user","pass","db") or die(mysqli_connect_error());

$sql        = "SELECT * FROM `tbl` where `cat` = 'desc';";
$result     = mysqli_query($_link,$sql) or die(mysqli_error($_link));
if(!mysqli_num_rows($result)) { die(mysqli_error($_link)); }

$sql2       = "SELECT * FROM `tbl` where `cat` = 'type';";
$result2    = mysqli_query($_link,$sql2) or die(mysqli_error($_link));
if(!mysqli_num_rows($result2)) { die(mysqli_error($_link)); }

$ar = array();
while($data1        = mysqli_fetch_assoc($result)) {
    $ar['modes'][$data1['mode']] = array(
        $data1['type'] => $data1['content']
    );
}
while($data2        = mysqli_fetch_assoc($result2)) {
    $ar['modes'][$data2['mode']][$data2['type']] = array(
        $data2['content']
    );
}

it displays [modes][mode1][desc], [modes][mode2][desc],[modes][mode3][desc], array correct but [modes][mode1][type], [modes][mode2][type], [modes][mode3][type] array contains only last row content

Answer 1

Try this code:

$_link = mysqli_connect("localhost","user","pass","db") or die(mysqli_connect_error());

$sql = "SELECT * FROM `tbl`;";
$result = mysqli_query($_link,$sql) or die(mysqli_error($_link));
if(!mysqli_num_rows($result)) { die(mysqli_error($_link)); }

$ar = array();

while($val = mysqli_fetch_assoc($result)) {
    $ar['modes'][$val['mode']][$val['cat']][] = $val['content'];
}

Answer 2

Change this:

$ar['modes'][$data2['mode']][$data2['type']] = array(
    $data2['content']
);

to this:

$ar['modes'][$data2['mode']][$data2['type']][] = $data2['content'];