Good Morning,
I ran into an issue trying to retrieve specific report data last night, and could use some help on the formulation of this select statement.
I am stepping through a list of $idmember values from 1 through 59 currently, and want to display the idmember along with the total number of 'A'ctive members 2 levels below them. My logic is to determine the number of members 1 level below them (records with up1_id = their idmember) and then take the idmember from those records, and count the number of records that have an up1_id equal to them. This should give me the count of members on second level down.. Right??
Sample data
idmember up1_id member_stat content
1 0 A whatever
2 1 A
3 2 A
4 2 A
5 3 A
6 4 A
7 4 A
Here is what I have, and it is returning ZERO for all counts
for ($x=1; $x<=$member_count; ++$x) {
$query="
SELECT count(*) from member a, member b, member c
WHERE a.idmember='$x'
AND a.member_stat='A'
AND c.member_stat='A'
AND b.up1_id=a.idmember
AND c.up1_id=b.idmember
";
$result=safe_query($query);
list($ct)=mysql_fetch_row($result);
echo"x is ".$x." and ct is ".$ct."<br>";
}
Given the sample data my resulting display should be
x is 1 and ct is 2 (3 and 4 on second level down)
x is 2 and ct is 3 (5, 6, and 7 on second level down)
x is 3 and ct is 0 (no second level members)
same for rest of entries
But I get the listing of the idmembers along with a ct value of 0 on all of them
Any suggestions / help would be greatly appreciated.
doug
