CODEWITHSUNDEEP
phpmysqlarrays
mickburkejnr
mickburkejnr

Reputation: 3690

How to group records from a database in a PHP foreach loop, then combine the values in to a new array?

I have a table in a MySQL database that is used to store games that someone can book on. The games can be held in more than one location but they can also share the same time as another game. Here is an example of the data in the array that is taken from the MySQL database:

[0] => Array
    (
        [id] => 174
        [gamedatetime] => DateTime Object
            (
                [date] => 2016-02-08 14:00:00.000000
                [timezone_type] => 3
                [timezone] => Europe/London
            )

        [available] => 1
        [gameID] => 1
        [isBooked] => 
    )

[1] => Array
    (
        [id] => 175
        [gamedatetime] => DateTime Object
            (
                [date] => 2016-02-08 14:00:00.000000
                [timezone_type] => 3
                [timezone] => Europe/London
            )

        [available] => 1
        [gameID] => 1
        [isBooked] => 1
    )

[2] => Array
    (
        [id] => 176
        [gamedatetime] => DateTime Object
            (
                [date] => 2016-02-08 15:00:00.000000
                [timezone_type] => 3
                [timezone] => Europe/London
            )

        [available] => 1
        [gameID] => 1
        [isBooked] => 
    )

What I need to do is create an array with this data on it, but grouped by the date and time, and the gameID are joined together but comma separated, like this:

[0] => Array
    (
        [gamedatetime] => 2016-02-08 14:00:00.000000
        [id] => 174,175
    )
[1] => Array
    (
        [gamedatetime] => 2016-02-08 15:00:00.000000
        [id] => 176
    )

How can I achieve this using PHP?

Upvotes: 0

Views: 40

Answers (3)

awinwood
awinwood

Reputation: 135

The following will achieve what you are aiming for but the ids will be in a subarray, allowing you to concatenate together as you need them.

$newArray = [];
foreach ($oldArray as $game) {
    $newArray[$game['gamedatetime']]['gamedatetime'] = $game['gamedatetime'];
    $newArray[$game['gamedatetime']]['ids'][] = $game['id'];
}

or you can change the query to something like:

SELECT gamedatetime, GROUP_CONCAT(id) as `id`
FROM game
WHERE ...
GROUP BY gamedatetime

Upvotes: 1

Gopalakrishnan
Gopalakrishnan

Reputation: 957

Try group_concat in query.

select gamedatetime, group_concat(`id` separator ',') as `id` from table group by gamedatetime;

Upvotes: 0

codeartha
codeartha

Reputation: 3

You would be more efficient by using the mysql GROUP BY word that would immediately return the array you want. If you just want to do it in php it's simply a matter of concatenate the results as you want them displayed which is not 100% clear to me in your question.

Upvotes: 0

Related Questions