好的,我会尽量详细和切中要点。
我有一张桌子。MemberConnections
它有3列——member1, member2, date
所以这就是我不知道该怎么做的。
我希望能够选择搜索日期。日期6/14/11 - 6/17/11。然后,我希望能够输入memberID并获得该成员在日期范围内"与"或"由"建立的所有连接的列表。
的例子:
6/14/11
- member1连接到member2
6/15/11
- member2连接到member4
- member4连接到member6
6/16/11
- member2连接到member5
- member1连接到member11
6/17/11
- 成员6连接到成员7
- member6连接到member8
- member16与member18连接
6/18/11
- member3连接到member9
- member5连接到member10
- member12连接到member14
希望这是有意义的。
好了,现在当我输入日期和UserID时,它应该返回所有从开始日期起已经连接的UserID。
按照我上面的例子。
该列表将包括:member1、member2、member3、member4、member5、member6、member7、member8、member9、member10和member11
但是member12, member14, member16和member18不会显示,因为它们没有与任何与搜索的USERID连接的人连接。
假设相关用户ID由$userID
表示,日期范围由$lo_date
和$hi_date
表示,那么您可以使用:
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;
请注意,UNION会自动消除所有重复项。
$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;
}