group array of object by dates in php

Question

Mysql Query:

select id, ad_click, contact_submit, MONTH(datetime) as months, YEAR(datetime) years from wpxc_leaderboard_counters order by datetime

it's showing result in this way:

Array ( 
    [0] => Array ( 
        [0] => stdClass Object ( 
            [id] => 2 [ad_click] => 0 [contact_submit] => 0 [datetime] => 2019-11-01 
        ) 
        [1] => stdClass Object ( 
            [id] => 3 [ad_click] => 0 [contact_submit] => 0 [datetime] => 2019-11-01 
        ) 
        [2] => stdClass Object ( 
            [id] => 4 [ad_click] => 1 [contact_submit] => 0 [datetime] => 2019-11-01 
        ) 
        [3] => stdClass Object ( 
            [id] => 5 [ad_click] => 0 [contact_submit] => 1 [datetime] => 2019-11-01 
        ) 
        [4] => stdClass Object ( 
            [id] => 1 [ad_click] => 3 [contact_submit] => 2 [datetime] => 2019-12-06 
        ) 
    )
)

but wanted to show in this way:

Array ( 
    [0] => Array ( 
        [11] => stdClass Object ( 
            [id] => 2 [ad_click] => 0 [contact_submit] => 0 [datetime] => 2019-11-01 
            [id] => 3 [ad_click] => 0 [contact_submit] => 0 [datetime] => 2019-11-01 
            [id] => 4 [ad_click] => 1 [contact_submit] => 0 [datetime] => 2019-11-01 
            [id] => 5 [ad_click] => 0 [contact_submit] => 1 [datetime] => 2019-11-01 
        )
        [12] => stdClass Object ( 
            [id] => 1 [ad_click] => 3 [contact_submit] => 2 [datetime] => 2019-12-06 
        ) 
    )
)

Hope this is possible with modifying array as i have used different methods and spent lots of time but nothing achieved yet.

Don't Panic · Accepted Answer · 2019-11-01 23:45:46Z

3

You can use a substring of the date as an array key as you fetch the query results.

while ($row = $query->fetch_object()) {
    $result[substr($row->datetime, 5, 2)][] = $row;
}

The array you showed doesn't quite match your query. If you're already selecting MONTH in the query, you won't need to use substr. You can use

$result[$row->months][] = $row;

I also suggest you consider grouping by the year as well as the month for future sorting purposes, but this probably won't matter for now since your query is ordered by datetime. It depends on how you intend to use the grouped data, but if you include year as part of the key you can be sure the groups will always sort properly.

edited Nov 1, 2019 at 23:45

answered Nov 1, 2019 at 23:29

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

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

1 Comment

Harry Over a year ago

Thanks a lot for your answer it helps me to build logic which was quite straight forward, I was just got stressed and unable to get the proper logic and here's what i got: $items = array(); foreach($sql as $row) { $items[$row->months][] = $row; }

Collectives™ on Stack Overflow

group array of object by dates in php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related