Merging php arrays if "clientid" is identical?

Question

Hi all, I have a query which gives back an array, I'd like to be able to merge the array values which have the same "clientid"

Here's my query:

SELECT * FROM #__db_clients_trip WHERE clientid IN (1999, 2984, 1681) AND companyid IN (1,2)

Here's my array:

stdClass Object
(
    [id] => 61
    [trip_code] => EUR0600
    [clientid] => 1999
    [date] => 2000-06-17
    [invoice] => 
    [aud] => 0
    [wsale] => 0
    [margin] => 
    [comments] =>
    [comments_by] => 
    [companyid] => 2
)

stdClass Object
(
    [id] => 89
    [trip_code] => 
    [clientid] => 2984
    [date] => 2000-03-18
    [invoice] => 
    [aud] => 0
    [wsale] => 0
    [margin] => 
    [comments] =>
    [comments_by] => 
    [companyid] => 2
)

stdClass Object
(
    [id] => 176
    [trip_code] => EUR0799
    [clientid] => 1999
    [date] => 1999-07-09
    [invoice] => 
    [aud] => 0
    [wsale] => 0
    [margin] => 
    [comments] =>
    [comments_by] => 
    [companyid] => 2
)

stdClass Object
(
    [id] => 281
    [trip_code] => EUR0299
    [clientid] => 1681
    [date] => 1999-03-01
    [invoice] => 30666
    [aud] => 1000
    [wsale] => 950
    [margin] => 
    [comments] =>
    [comments_by] => 
    [companyid] => 2
)

stdClass Object
(
    [id] => 296
    [trip_code] => EUR0799
    [clientid] => 1681
    [date] => 1999-07-15
    [invoice] => 
    [aud] => 0
    [wsale] => 0
    [margin] => 
    [comments] =>
    [comments_by] => 
    [companyid] => 2
)

Is this possible?

EDIT:

To either show a single row per client id, or to merge the array like so:

stdClass Object
(
    [id] => 61
    [trip_code] => EUR0600, EUR0799
    [clientid] => 1999
    [date] => 2000-06-17, 1999-07-09
    [invoice] => 
    [aud] => 0, 0
    [wsale] => 0, 0
    [margin] => 
    [comments] =>
    [comments_by] => 
    [companyid] => 2, 2
)

^^ or something like that? I'm guessing a single row per clientid would be easier..?

Answer 1

If you just want a single row, add this to the end of the SQL:

GROUP BY clientid

If you want to get (for instance), the latest date for all the records matching a clientid, change:

SELECT *

To:

SELECT *, MAX(date) AS latest_date

You can use functions like MAX, MIN and SUM on fields to get a 'merged' view when using GROUP BY

Answer 2

sounds like you should loop over your result set and do the merge yourself... Use a 2 dimensional array...

Answer 3

If all you care about is a distinct clientID, then you should use the Distinct keyword:

select distinct(clientID) from clients