CODEWITHSUNDEEP
phpmysqlarrays
Harry
Harry

Reputation: 113

group array of object by dates in php

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.

Upvotes: 1

Views: 599

Answers (1)

Don't Panic
Don't Panic

Reputation: 41810

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.

Upvotes: 3

Related Questions