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

Hmm...was my post removed? I swear I had posted it and then it was no longer there when I refreshed. If Im breaking the rules, can someone tell me how Im breaking the rules and then remove it. Thanks!

[HuggieBear]

Give this a try...

[code]<?php
// Connect to your database
include('connect.php');

// Define the query (using the GROUP BY feature, look it up in the MySQL manual)
$sql = "SELECT DATE_FORMAT(START_DATE,'%Y%m') AS date, TEAM_ID as teamid FROM sf_team_player WHERE OLDCLUB_ID IS NOT NULL GROUP BY teamid ORDER BY date DESC, teamid";

// Execute the query or error
$result = mysql_query($sql) OR mysql_error();

// Stick the results into a multi-dimensional array keyed on date
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  if (!isset($data[$row['date']])){
      $data[$row['date']] = array($row['teamid']); // Assign the first item of the array
  }
  else {     
      array_push($data[$row['date']], $row['teamid']); // Assign additional items into the array
  }
}

// This is here to show you the structure of the array
echo "<pre>\n";
print_r($data);
echo "</pre>\n";
?>[/code]

If you're not sure what any of it's doing then please ask.

Regards
Huggie