query/array question....

[mkosmosports]

I have a mysql table that consists of the following columns: START_DATE, TEAM_ID.
What I want to do is run a query that retrieves distinct START_DATE values and then all of the distinct TEAM_ID values that are in the same result row as the distinct START_DATE values. In case Im being a little confusing, in mysql I have:
|START_DATE|TEAM_ID|
|200608|6|
|200608|6|
|200608|7|
|200608|7|
|200609|4|
|200609|4|

What Id like to do then is retrieve the unique dates, and the unique teamids for that date, so something like:|200608 => 6,7|200609 => 4|

This is what I have so far:
$test3 = @mysql_query("SELECT DISTINCT DATE_FORMAT(START_DATE,'%Y%m') AS date, TEAM_ID AS teamid from sf_team_player WHERE OLDCLUB_ID IS NOT NULL ORDER BY START_DATE DESC");
$rows = mysql_num_rows($test3);

for($i=0;$i<$rows;$i++)
{
$tid = mysql_result($test3,$i,'teamid');
$date = mysql_result($test3,$i,'date');
$testarr[$date] = $tid;
}

The above returns an array telling me the number of teamids (theyre not unique though, which is part of the problem) that exist whenever the START_DATE is $date. Now how can I bring that teamid number down by ignoring duplicates (Im having trouble adjusting my query for that, Im pretty sure that is where the problem is) and creating a multidimensional query that will not only tell me the number of unique teamids whenever START_DATE is $date but also show them to me?

Help much appreciated...
mkosmosports

[btherl]

I'm surprised that the teamids are not unique from that query.  DISTINCT should return unique rows only.

To get the structure you want, I would use
[code=php:0]$testarr[$date][] = $tid[/code]

Then you get the teamids for each date in an array.

I'm interested to see what your actual output is from that query.

[HuggieBear]

I replied to this in the PHP Help forum, please try not to double post.

http://www.phpfreaks.com/forums/index.php/topic,122598.0.html

Regards
Huggie