php，将结果组合成多维关联数组

Question

我有以下MySQL数据库：

    | id         | sessionId   | time                |
    |------------|-------------|---------------------|
    | 54         |           4 | 2013-03-23 09:00:00 |
    | 55         |           4 | 2013-03-23 09:15:00 |
    | 56         |           5 | 2013-04-20 01:00:00 |
    | 57         |           5 | 2013-04-20 15:15:00 |
    | 58         |           5 | 2013-04-20 19:15:00 |

我正试图按如下方式返回一个数组：

    Array
    (
        [0] => Array
            (
                [sessionId] => 4
                [time] => Array
                    (
                        [0] => 2013-03-23 09:00:00
                        [1] => 2013-03-23 09:15:00
                    )
            )
        [1] => Array
            (
                [sessionId] => 5
                [time] => Array
                    (
                        [0] => 2013-04-20 01:00:00
                        [1] => 2013-04-20 15:15:00
                        [2] => 2013-04-20 19:15:00
                    )
            )
    )

到目前为止运行这个查询

    $result = mysql_query("SELECT * FROM `Exercise_Lists_New` WHERE `patientId` = 381");
    $row = mysql_fetch_assoc($result);
    while($row = mysql_fetch_array($result)){
        $sessionIds[] = array("sessionId" =>$row['sessionId'], "time" => array($row['time']));
    }
    print_r($sessionIds,true);

给我下面的

    Array
        (
            [0] => Array
                (
                    [sessionId] => 4
                    [time] => Array
                        (
                            [0] => 2013-03-23 09:00:00
                        )
                )
            [1] => Array
                (
                    [sessionId] => 4
                    [time] => Array
                        (
                            [0] => 2013-03-23 09:15:00
                        )
                )
            [2] => Array
                (
                    [sessionId] => 5
                    [time] => Array
                        (
                            [0] => 2013-04-20 01:00:00
                        )
                )
            [3] => Array
                (
                    [sessionId] => 5
                    [time] => Array
                        (
                            [0] => 2013-04-20 15:15:00
                        )
                )
            [4] => Array
                (
                    [sessionId] => 5
                    [time] => Array
                        (
                            [0] => 2013-04-20 19:15:00
                        )
                )
        )

所以我想把所有共享同一"sessionId"的"时间"分组在一起。使用php做这件事的最佳方式是什么。

感谢

Answer 1

只需尝试：

while($row = mysql_fetch_array($result)){
    if ( !isset($sessionIds[$row['sessionId']]) ) {
        $sessionIds[$row['sessionId']] = array(
            'sessionId' => $row['sessionId'],
            'time'      => array()
        );
    }
    $sessionIds[$row['sessionId']]['time'][] = $row['time'];
}

Answer 2

SELECT
    sessionId,
    GROUP_CONCAT(time) AS `Times`
FROM mytable
GROUP BY    sessionId

您可以使用mysql的group_concat。这将为您提供逗号分隔字段的结果，您可以使用php分解该字段以获得数组。

输出：

| sessionId   | time                                                           |
|-------------|--------------------------------------------------------------- |
|           4 | 2013-03-23 09:00:00 , 2013-03-23 09:15:00                      |
|           5 | 2013-04-20 01:00:00 , 2013-04-20 15:15:00 , 2013-04-20 19:15:00|

编辑：

$query  =   "   SELECT
                    sessionId,
                    GROUP_CONCAT(time) AS `Times`
                FROM mytable
                GROUP BY    sessionId";
$result = mysqli_query($query);
$row = mysqli_fetch_assoc($result);
$result_array   =   array();
$i=0;
while($row = mysql_fetch_array($result)){
    $result_array[$i]['sessionId']  = $row['sessionId'];
    $result_array[$i]['time']       = explode(',',$row['Times']);
    $i++;
}
print_r($result_array,true);

结果

Array
    (
        [0] => Array
            (
                [sessionId] => 4
                [time] => Array
                    (
                        [0] => 2013-03-23 09:00:00
                        [1] => 2013-03-23 09:15:00
                    )
            )
        [1] => Array
            (
                [sessionId] => 5
                [time] => Array
                    (
                        [0] => 2013-04-20 01:00:00
                        [1] => 2013-04-20 15:15:00
                        [2] => 2013-04-20 19:15:00
                    )
            )
    )   

Answer 3

从该数组中剥离一级，并使用sessionID作为密钥，例如

$sessionIDs = array();
while($row = mysql_fetch_array($result)){
    $sessionIds[$row['sessionID']][] = $row['time'];
}

这会给你一些类似的东西：

$arr = array(
     'id' => array(time1, time2, time3, ...),
     'id' => arraY(...)
     etc..
);

请注意，当您在循环的OUTSIDE中调用fetch()时，代码中会丢失一行，然后在foreach循环中立即用fetch调用覆盖该$row。

Answer 4

尝试使用此

<?php
$result = mysql_query("SELECT * FROM `Exercise_Lists_New` WHERE `patientId` = 381");
$row = mysql_fetch_assoc($result);
while($row = mysql_fetch_array($result)){    
    $sessionIds[$row['sessionId']]['sessionId'] = $row['sessionId'];
    $sessionIds[$row['sessionId']]['time'][] = $row['time'];
}
print_r($sessionIds):
Array
   (
       [4] => Array
           (
               [sessionId] => 4
               [time] => Array
                   (
                       [0] => 2013-03-23 09:00:00
                       [1] => 2013-03-23 09:15:00
                   )
           )
       [5] => Array
           (
               [sessionId] => 5
               [time] => Array
                   (
                       [0] => 2013-04-20 01:00:00
                       [1] => 2013-04-20 15:15:00
                       [2] => 2013-04-20 19:15:00
                   )
           )
   )