Grouping arrays by values

Posted by chaloum on December 14, 2012 at 4:55am

I wanting the group the output of an db query in to separate tables the rendered output would be grouped by the a field within the array.

The queries output is

Array
(
[0] => stdClass Object
(
[id] => 10
[aid] => Day_1-Session_1
[uid] => 1
[tid] => 211
[eid] => 207
[event_name] => City 1
[field_event_dates_value] => 2013-03-12 00:00:00
[topic] => Test 1
[field_date_value] => 2013-03-12 00:00:00
[field_start_time_value] => 9:00AM
[field_fname_value] => jim
[field_lname_value] => jones
[mail] => someemailaddress
)

[1] => stdClass Object
(
[id] => 11
[aid] => Day_1-Session_2
[uid] => 8
[tid] => 211
[eid] => 207
[event_name] => City 1
[field_event_dates_value] => 2013-03-12 00:00:00
[topic] => Test 1
[field_date_value] => 2013-03-12 00:00:00
[field_start_time_value] => 9:00AM
[field_fname_value] => jim
[field_lname_value] => jones
[mail] => someemailaddress
)

[2] => stdClass Object
(
[id] => 12
[aid] => Day_2-Session_1
[uid] => 1
[tid] => 211
[eid] => 207
[event_name] => City 1
[field_event_dates_value] => 2013-03-13 00:00:00
[topic] => Test 1
[field_date_value] => 2013-03-13 00:00:00
[field_start_time_value] => 9:00AM
[field_fname_value] => jim
[field_lname_value] => jones
[mail] => someemailaddress
)

[3] => stdClass Object
(
[id] => 11
[aid] => Day_2-Session_2
[uid] => 8
[tid] => 211
[eid] => 207
[event_name] => City 1
[field_event_dates_value] => 2013-03-13 00:00:00
[topic] => Test 1
[field_date_value] => 2013-03-13 00:00:00
[field_start_time_value] => 9:00AM
[field_fname_value] => jim
[field_lname_value] => jones
[mail] => someemailaddress
)

)

and I'm (in this example) after two tables. The first table displaying items grouped by the [field_event_dates_value] field something like

Date | Session | Start Time |
2013-03-12 | Day_2-Session_1 | 9:00AM |
2013-03-12 | Day_2-Session_2 | 9:00AM |

Date | Session | Start Time |
2013-03-13 | Day_2-Session_1 | 9:00AM |
2013-03-13 | Day_2-Session_2 | 9:00AM |

so it would be similar in function to the way views groups by fields and then returns separate tables on the field values

Any one know how to do this.

thanks

Marcel

Comments

The obvious question is why

Posted by nevets on December 14, 2012 at 5:16am

The obvious question is why not use views?

If using db_query you want to add a "Sort By" clause to the query. Your loop through the results will then be responsible for formatting the output.

that's not the question I'm

Posted by chaloum on December 14, 2012 at 5:23am

that's not the question I'm asking

http://edmedia.com.au

If you want the array grouped

Posted by nevets on December 14, 2012 at 5:33am

If you want the array grouped you need to sort it. But you can do that directly in the query.

no I need to to work with

Posted by chaloum on December 14, 2012 at 5:45am

no I need to to work with out sorting in sql

http://edmedia.com.au

Then you will need to use the

Posted by nevets on December 14, 2012 at 5:47am

Then you will need to use the appropriate PHP array sort function (look up array_sort, on the PHP site there will be a table comparing the various functions).

this is what I've got so

Posted by chaloum on December 14, 2012 at 11:14am

this is what I've got so far.

So the code below is splitting in to tables based on an event date

$grouping=array('field_event_dates_value');

if($records=$result->fetchAll()) {

foreach ($records as $record){
if (!array_key_exists($record->$grouping, $new_array)){
$new_array[$record->field_fname_value][] = $record;

}
}
}

//break into separate tables
foreach($new_array as $cells){
$output.=$cells[key($cells)]->field_fname_value. ' '. $cells[key($cells)]->field_lname_value;

$rows=array();
$i=0;
foreach($cells as $row){
$rows[]=array_map('check_plain', (array) $row);
}
$output.=theme('table',array('header'=>$header, 'rows'=>$rows));
}

I need to make the code more agnostic as I want the data regrouped without having to hard code the groupings. I need to work out the levels that the grouping occurs and then dynamically group and with out knowledge of the data output and only reacting to the grouping variables in the array.

it needs to be something like a tree

http://edmedia.com.au

I think this will get the

Posted by bnjmnm on December 17, 2012 at 12:42am

I think this will get the array assembled in the manner you require.

$new = array();
foreach ($records as $record){
    //each array key is a timestamp, so you can sort it
    //there's no need to use an in_array check with this, either
    $new[strtotime($record->field_date_value)][] = array('Date' => $record->field_date_value,
                                                         'Session' => $record->aid,
                                                         'Time' => $record->field_start_time_value);
}
//sort by array keys in reverse order
krsort($new);
//your array is now sorted in the order you want, and the keys all correspond to a specific date
//use php date() if you want to convert the timestamp to different output

bnjmnm I realised after that

Posted by chaloum on December 18, 2012 at 12:35am

bnjmnm

I realised after that I didn't need the check to see if the array already existed, where I'm at is this is ok for one level, for instance a person registers for and event (as in the current example) all the activities/session are grouped be the events date. But is I wanted to break it down so that the we have the event and the activities grouped per day in separate tables, do the result would be something like.

Event

--Day 1
----Session 1
----Session 2
----Session 3
----Session 4

--Day 2
----Session 1
----Session 2
----Session 3
----Session 4

--Day 3
----Session 1
----Session 2
----Session 3
----Session 4

etc..

any ideas on how this might be done?

thanks

http://edmedia.com.au

I'm not able to actually test

Posted by bnjmnm on December 18, 2012 at 3:14pm

I'm not able to actually test this code, but this is roughly how you'd do that (assuming I interpreted the question properly). Each date is a separate table, and the table contents are chronologically ordered events occurring on that date.

$days = array();
foreach ($records as $record){
   
    $days[strtotime($record->field_date_value)][strtotime($record->field_start_time_value)] = $record->aid;
}
ksort($days);

foreach($days as &$events)
{
    ksort($events);
}

foreach($days as $day => $time){
    $rows = array();
    foreach($time as $tim => $summary){
        $rows[] = array('data' => array(
            $summary,
            date("h:i:a", $tim))
      
        );
    }
    print theme('table', 
            array('header' => $header,
                  'rows'=>$rows,
                  'caption' => date("Y-m-d", $day)));
}

Thanks, I think i've been

Posted by chaloum on December 19, 2012 at 12:23am

Thanks, I think i've been over thinking the issue

foreach ($records as $record){
$new[$record->uid][strtotime($record->field_date_value)][$record->field_date_value] = array('Date' => $record->field_date_value,
'Session' => $record->aid,
'Time' => $record->field_start_time_value);
}

seems to do the trick.

thanks

http://edmedia.com.au