Connecting Results by Date - Can this even be done?

OK so I will try to be as detailed and to the point as possible.

I have a table. MemberConnections

It has 3 columns - member1, member2, date

So this is what I cannot figure out to do.

I want to be able to pick search dates. Example Date 6/14/11 - 6/17/11. Then I want to be able to enter a memberID and get a list of all connections that have been made 'to' or 'by' that member during the date range.

Example:

6/14/11

• member1 connects with member2

6/15/11

• member2 connects with member4
• member4 connects with member6

6/16/11

• member2 connects with member5
• member1 connects with member11

6/17/11

• member 6 connects with member7
• member6 connects with member8
• member16 connects with member18

6/18/11

• member3 connects with member9
• member5 connects with member10
• member12 connects with member14

Hopefully that makes sense.

Ok so now when I enter the dates and an UserID it should get back all userid's that are and have connected since the beginning date.

So going by my example above.

The list would include: member1, member2, member3, member4, member5, member6, member7, member8, member9, member10, and member11

But member12, member14, member16, and member18 would no开发者_JAVA技巧t show as they did not connect with anyone who was connected with the USERID that was searched for.

Assuming that the relevant user ID is represented by $userID and that the date range is represented by $lo_date and $hi_date, then you could use:

SELECT Member1 AS Member
  FROM MemberConnections
 WHERE Member2 = $userID
   AND Date BETWEEN $lo_date AND $hi_date
UNION
SELECT Member2 AS Member
  FROM MemberConnections
 WHERE Member1 = $userID
   AND Date BETWEEN $lo_date AND $hi_date;

Note that the UNION eliminates any duplicates automatically.

$member = $_GET['member'];
$date = $_GET['date'];

$firstRows = getMemberConnections($member)
$connections = array_merge($firstRows, getTree($firstRows));

$connectedMembers = array();
foreach ($connections as $connection)
{
    $connectedMembers[] = $connection['member1'];
    $connectedMembers[] = $connection['member2'];
}

$connectedMembers = array_unique($connectedMembers);
print_r($connectedMembers);


function getTree($rows)
{
    $subTree = array();
    foreach ($rows as $row)
    {
        $subTree[] = getMemberConnections($row['member1']);
        $subTree[] = getMemberConnections($row['member2']);
    }

    $subTree = array_unique($subTree);
    $subTree = array_merge($subTree, getTree($subTree));
    return $subTree;    
}

function getMemberConnections($member)
{
    global $date;
    $rows = array()
    $result = mysql_query("SELECT member1, member2 FROM MemberConnections WHERE (member1='$member' or member2='$member') and date >= '$date'") or die mysql_error();
    while ( $row = mysql_fetch_assoc($result) ) 
    {
        $rows[] = $row;
    }
    return $rows;
}