Group a CodeIgniter query's result set by a column and create subarrays in each group

Question

I am trying to populate a multidimensional array with CodeIgniter. In the code below, the outer foreach loops 2 times. The inner foreach loops 3 times and then 2 times. The problem I am having is that my resulting $data['tc'] contains a single array containing 5 items, but I want to end up with 2 arrays containing 2 and 3 items respectively.

$bd = $this->db->query("SELECT tbltc.BILLED FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario GROUP BY BILLED");

foreach ($bd->result() as $bdrow) {
    $tcbd = $this->db->query("SELECT tbltc.BILLED, tbltc.TC, tbltc.CAT FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario AND tbltc.BILLED = '".$bdrow->BILLED."' GROUP BY TC");
    
    foreach ($tcbd->result() as $row) {
        $tmp[] = array( $row->BILLED => $row->TC);
    }

    $data['tc'] = $tmp; 
}

Currently I am receiving an array that looks like this:

Array
(
    [0] => Array
        (
            [2011-11-18 00:00:00] => C
        )
    [1] => Array
        (
            [2011-11-18 00:00:00] => I
        )
    [2] => Array
        (
            [2011-11-18 00:00:00] => S
        )
    [3] => Array
        (
            [2011-11-22 00:00:00] => C
        )
    [4] => Array
        (
            [2011-11-22 00:00:00] => S
        )
)

But I need this to be broken into two separate arrays, one for the 2011-11-18 date, and one for the 2011-11-22 date.

is what you want an array in the specific billed dates containing an array of the TC responses with ID? — topherg
– topherg, Commented Nov 26, 2011 at 23:30
Yes cgoddard, that is what I am looking for. I want TC to be grouped by billed dates. Thanks. Your answer below is not grouped by billed date. — DanielAttard
– DanielAttard, Commented Nov 26, 2011 at 23:41

Alexey Gerasimov · Accepted Answer · 2011-12-01 04:25:56Z

You actually have a bunch of issues in your code. Unless I am missing something, your queries are redundant:

"SELECT tbltc.BILLED FROM tbltc 
WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario 
GROUP BY BILLED"

"SELECT tbltc.BILLED, tbltc.TC, tbltc.CAT FROM tbltc 
WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario 
AND tbltc.BILLED = '".$bdrow->BILLED."' 
GROUP BY TC"

You are selecting data from the same table based on same $pn, $taxyear, and $scenario. Once you get the first result set you loop through it just to pass BILLED into the second query. That's completely unnecessary and can be accomplished with 2nd query alone by removing $bdrow->BILLED

"SELECT tbltc.BILLED, tbltc.TC, tbltc.CAT FROM tbltc 
WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario 
GROUP BY TC"

Second, you have GROUP BY in both queries but don't have any count(), sum(), or other aggregate functions. What are trying to achieve by using GROUP BY? So, let's take it out now for the sake of exercise. You now have one query with a cartesian result. Here's the code

$tcbd = $this->db->query("SELECT tbltc.BILLED, tbltc.TC, tbltc.CAT FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario");
$data = array();
foreach ($tcbd->result() as $row) {
    $data[$row->BILLED][] = $row->TC;
}

will give you

Array
(
    [2011-11-18 00:00:00] => Array
        (
             [0] => C,
             [1] => I,
             [2] => S
        ),
    [2011-11-22 00:00:00] => Array
        (
             [0] => C,
             [1] => S
        )
)

key positions might vary - I simply typed the results based on your data

topherg · Accepted Answer · 2011-11-26 23:26:27Z

0

$tmp = array();
$dataarray = array();

foreach ($tcbd->result() as $row) {
    $dataarray['BILLED'] = $row->BILLED;
    $dataarray['TC'] = $row->TC;

    $tmp[] = $dataarray;
}

that will create:

Array
(
    [0] => Array
        (
            ['BILLED'] => 2011-11-18 00:00:00
            ['TC'] => C
        )

    [1] => Array
        (
            ['BILLED'] => 2011-11-18 00:00:00
            ['TC'] => I
        )

    [2] => Array
        (
            ['BILLED'] => 2011-11-18 00:00:00
            ['TC'] => S
        )

    [3] => Array
        (
            ['BILLED'] => 2011-11-18 00:00:00
            ['TC'] => C
        )

    [4] => Array
        (
            ['BILLED'] => 2011-11-18 00:00:00
            ['TC'] => S
        )
)

answered Nov 26, 2011 at 23:26

topherg

4,3314 gold badges40 silver badges74 bronze badges

3 Comments

ariefbayu Over a year ago

I think, what he want is: $tmp[$billed][$tc]

DanielAttard Over a year ago

cgoddard - thanks for your help. referring to the array you have created above, I know how to use $query->num_rows(); to give me the total number of rows, but what if I wanted to know the total number of rows where BILLED = 2011-11-18 ?? Is there a way to restrict the num_rows() function and pass in criteria?

topherg Over a year ago

@DanielAttard You could do what silent said and that creates the array within the $tmp with the key being $billed. But if you just want that, why don't you use a WHERE tag in the query and count the resulting rows. Or create an array with just the $billed data and use a count array function (cant remember the name) that creates an array with list of values and the number of times each one occurred.

malletjo · Accepted Answer · 2011-11-27 00:04:27Z

Before :

$bd = $this->db->query("SELECT tbltc.BILLED FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y =         $taxyear AND tbltc.SCENARIO = $scenario GROUP BY BILLED");

foreach ($bd->result() as $bdrow) {
    $tcbd = $this->db->query("SELECT tbltc.BILLED, tbltc.TC, tbltc.CAT FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario AND tbltc.BILLED = '".$bdrow->BILLED."' GROUP BY TC");

    foreach ($tcbd->result() as $row) {
        $tmp[] = array( $row->BILLED => $row->TC);
    }

    $data['tc'] = $tmp; 
}

$tmp is never reset
$data['tc'] will always be overwrite each time you loop
should initialize your variable

After:

$bd = $this->db->query("SELECT tbltc.BILLED FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y =         $taxyear AND tbltc.SCENARIO = $scenario GROUP BY BILLED");
$data = array();
foreach ($bd->result() as $bdrow) {
    $tcbd = $this->db->query("SELECT tbltc.BILLED, tbltc.TC, tbltc.CAT FROM tbltc WHERE tbltc.PN = $pn AND tbltc.Y = $taxyear AND tbltc.SCENARIO = $scenario AND tbltc.BILLED = '".$bdrow->BILLED."' GROUP BY TC");
    $tmp = array();
    foreach ($tcbd->result() as $row) {
        $tmp[] = array( $row->BILLED => $row->TC);
    }
    $data[] = $tmp; 
}

This should output something like this :

Array()
    [0] => Array() 
          [0] => X
          [1] => Y
    [1] => Array()
          [0] => Z
          ...
    ...

thanks racar. This looks good. I am going to test it out now and see if i can get it to work.

mickmackusa · Accepted Answer · 2025-03-10 23:53:57Z

To query the database and return the grouped data using functional-style code, use array_reduce().

return array_reduce(
    $this->db
        ->get_where('tbltc', ['PN' => $pn, 'Y' => $taxyear, 'SCENARIO' => $scenario])
        ->result(),
    function ($result, $row) {
        $result[$row->BILLED][] = $row->TC;
        return $result;
    },
    []
);

If you prefer a language construct loop, here's foreach().

$resultSet = $this->db
    ->get_where('tbltc', ['PN' => $pn, 'Y' => $taxyear, 'SCENARIO' => $scenario])
    ->result();
$result = [];
foreach ($resultSet as $row) {
    $result[$row->BILLED][] = $row->TC;
}
return $result;

If you want to order the result sets, use order_by('BILLED') before the get_where() method is chained.

Collectives™ on Stack Overflow

Group a CodeIgniter query's result set by a column and create subarrays in each group

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related