在php和mysql中创建多维数组

Question

Currently I am trying create an array multidimensional in php using two queries (Items and Categories). I made a search in the site but I did not found anything similar to what I am looking for. I appreciate if someone can help me using my code.

Please se bellow what am looking for and my code.

Tables:

TABLE Items;

+-----------------------------------+
|    id    |    type    |    name   |
+----------+------------+-----------+
|     1    |     4      |  item_1   |       
|     2    |     3      |  item_2   |
|     3    |     2      |  item_3   |
+-----------------------------------+

TABLE Categories;

+-----------------------------------+
|    id    |   Item_id  |    name   |
+----------+------------+-----------+
|     1    |     2      |   Cat_a   |       
|     2    |     2      |   Cat_b   |
|     3    |     3      |   Cat_x   |
|     4    |     3      |   Cat_z   |
|     5    |     3      |   Cat_b   |
|     6    |     1      |   Cat_b   |
|     7    |     3      |   Cat_y   |
+-----------------------------------+

Result that I am looking for:

Array
(
    [0] => Array
        (
            id   => 1
            name => Item_1
            Type => 4
            cats => Array
                (
                    [6] => Cat_b
                )
        )
    [1] => Array
        (
            id   => 2
            name => Item_2
            Type => 3
            cats => Array
                (
                    [1] => Cat_a
                    [2] => Cat_b
                )
        )
    [2] => Array
        (
            id   => 3
            name => Item_3
            Type => 2
            cats => Array
                (
                    [3] => Cat_x
                    [4] => Cat_z
                    [5] => Cat_b
                    [7] => Cat_y
                )
        )
)

My code:

$result = mysqli_query($link, "SELECT * FROM Categories WHERE Item_id = '233'");


foreach ($result as $key => $value) {
    $v[] = $value["id"];
}

    foreach ($v as $key => $res) {
        $query = mysqli_query($link, "SELECT * FROM Items WHERE category_id = '".$res."'");
           foreach ($query as $k =>$att){
                 $var[$res][] =  $att["name"]; 
          }
    }

    echo '<pre>' . print_r($var,1) . '</pre>';  

Answer 1

Use the following, Tested and working

$sql = "SELECT i.id,i.name,i.type,c.id AS CatKey,c.name As catName FROM `Items` AS i JOIN Categories AS c ON i.id=c.Item_id ORDER BY i.id ASC ";
$result = $conn->query($sql);
$res = array();
$i = 0;
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
       $ids = array_column($res, 'id');
       if(in_array($row['id'], $ids)){
            $res[$i-1]['cats'][$row['CatKey']] = $row['catName'];
       }else{
        $res[$i]['id']   = $row['id'];
        $res[$i]['type'] = $row['type'];
        $res[$i]['name'] = $row['name'];
        $res[$i]['cats'] = array($row['CatKey'] => $row['catName']);
        $i++;
       }
    }
} 
echo '<pre>';
print_r($res);

Result:-

Array
(
    [0] => Array
        (
            [id] => 1
            [type] => 4
            [name] => item_1
            [cats] => Array
                (
                    [6] => Cat_b
                )

    )

[1] => Array
    (
        [id] => 2
        [type] => 3
        [name] => item_2
        [cats] => Array
            (
                [1] => Cat_a
                [2] => Cat_b
            )

    )

[2] => Array
    (
        [id] => 3
        [type] => 2
        [name] => item_3
        [cats] => Array
            (
                [3] => Cat_x
                [4] => Cat_z
                [5] => Cat_b
                [7] => Cat_y
            )

    )

)