php, mysql - Combine result into multidimension associate array

Question

I have the following MySQL database :

    | 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 |

I am trying to return an array as so:

    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
                    )

            )

    )

So far running this query

    $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);

Gives me the follwing

    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
                        )

                )

        )

So i would like to group all the 'time' that share the same 'sessionId' together. What would be the best way to do this using php.

Thanks

If you want the data you get from the query GROUP'ed, why do you insist on doing so in PHP? Why not use GROUP_CONCAT, GROUP BY, or any other way of grouping a resultset. Also: Stop using the deprecated mysql_* extension — Elias Van Ootegem
– Elias Van Ootegem, Commented Mar 20, 2013 at 14:12

hsz · Accepted Answer · 2013-03-20 14:14:30Z

2

Just try with:

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'];
}

answered Mar 20, 2013 at 14:14

hsz

153k63 gold badges268 silver badges320 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Raheel · Accepted Answer · 2013-03-20 14:23:45Z

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

You can use group_concat of mysql for this. This will give you results as comma seperated field which you can explode with php to get an array.

Output :

| 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|

EDITED :

$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);

Result

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
                    )

            )
    )

Marc B · Accepted Answer · 2013-03-20 14:12:56Z

0

Strip one level off that array, and use sessionID as the key, e.g.

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

This'll give you something like:

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

Note that you're losing one row in your code, as you do a fetch() call OUTSIDE of your loop, then immediately overwrite that $row with the fetch call in the foreach loop.

answered Mar 20, 2013 at 14:12

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

rkmax · Accepted Answer · 2013-03-20 14:55:53Z

0

Try with this

<?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
                   )
           )

   )

edited Mar 20, 2013 at 14:55

answered Mar 20, 2013 at 14:16

rkmax

18.2k23 gold badges96 silver badges182 bronze badges

1 Comment

Alex McDaid Over a year ago

this gives me multiple sessionIDs

Collectives™ on Stack Overflow

php, mysql - Combine result into multidimension associate array

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related