Nested loops problem.

[MikEst]

Hi,

 

I have two tables, one consists of

ID, PlayerName, PlayerID, Height, Weight etc

 

the other consists of

ID, PlayerIDnumb, PointsScored, Rebounds, Assists, SeasonNumb etc (multiple rows for each PlayerIDnumb)

 

I need it to print out:

 

Name: "PlayerName1", Points: "PointsScored_sum", Season: "SeasonNumb1"

Name: "PlayerName1", Points: "PointsScored_sum", Season: "SeasonNumb2"

Name: "PlayerName1", Points: "PointsScored_sum", Season: "SeasonNumb3"

...

Name: "PlayerName1", Points: "PointsScored_sum", Season: "SeasonNumb8"

Name: "PlayerName2", Points: "PointsScored_sum", Season: "SeasonNumb1"

Name: "PlayerName2", Points: "PointsScored_sum", Season: "SeasonNumb2"

Name: "PlayerName2", Points: "PointsScored_sum", Season: "SeasonNumb3"

...

Name: "PlayerName2", Points: "PointsScored_sum", Season: "SeasonNumb8"

etc.

 

I tried to do it like this:

$query1="SELECT * FROM table1";
$result1=mysql_query($query1);


while(list($ID, $PlayerName, $PlayerID)=mysql_fetch_array($result1)){

$Start_season=1;
$Season_c=8;

for($Start_season; $Start_season<=$Season_c; $Start_season++){		

	$query="SELECT * FROM table2 WHERE PlayerIDnumb = '$PlayerID' AND Season=$Start_season";		
	$result=mysql_query($query);

	while(list($PlayerIDnumb, $PointsScored) = mysql_fetch_row($result)){
		$PS_sum=$PS_sum+$PointsScored;
	}

	echo "Name: $PlayerName, Points: $PS_sum, Season: $Start_season<br />";

	$PS_sum=0;


}

$PS_sum=0;
    
}

but it prints out:

 

Name: "PlayerName1", Points: "PointsScored_sum", Season: "SeasonNumb1"

Name: "PlayerName1", Points: "PointsScored_sum_all", Season: "SeasonNumb2"

Name: "PlayerName1", Points: "PointsScored_sum_all", Season: "SeasonNumb3"

...

Name: "PlayerName1", Points: "PointsScored_sum_all", Season: "SeasonNumb8"

 

 

 

 

When I echo the "$query", it prints out:

 

SELECT * FROM table2 WHERE PlayerIDnumb = 'PlayerID1' AND Season=1

SELECT * FROM table2 WHERE PlayerIDnumb = '' AND Season=2

SELECT * FROM table2 WHERE PlayerIDnumb = '' AND Season=3

...

SELECT * FROM table2 WHERE PlayerIDnumb = '' AND Season=8

 

 

 

 

Why does

while(list($ID, $PlayerName, $PlayerID)=mysql_fetch_array($result1)){
...
}

loop only once?

 

And why does the PlayerIDnumb disappear?

[GingerRobot]

Ouch...you really don't need to to loop through the results and execute a query for each player. You can use a join. I think this should work for you:

 

SELECT table1.PlayerName, SUM(table2.PointsScored),table2.SeasonNum FROM table1,table2 WHERE table1.PlayerID=table2.PlayerIDnumb GROUP BY(table1.PointsScored) ORDER BY table1.PlayerName, table2.SeasonNum

 

If you then loop through those results, you should get what you need:

 

$sql = "SELECT table1.PlayerName, SUM(table2.PointsScored),table2.SeasonNum FROM table1,table2 WHERE table1.PlayerID=table2.PlayerIDnumb GROUP BY(table1.PointsScored) ORDER BY table1.PlayerName, table2.SeasonNum";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

while(list($name,$score,$season) = mysql_fetch_row($result)){
    echo "$name, $score, $season <br>";
}

 

Edit: You can find a tutorial on joins here: http://www.phpfreaks.com/tutorial/data-joins-unions

[MikEst]

Can I do it somehow that this

while(list($PlayerIDnumb, $PointsScored) = mysql_fetch_row($result)){
     $PS_sum=$PS_sum+$PointsScored;
}

remains the same?

 

I don't really want to change it as it's actually as an include and also used elsewhere.

[MikEst]

Anyone?

[GingerRobot]

Can I do it somehow that this

while(list($PlayerIDnumb, $PointsScored) = mysql_fetch_row($result)){
     $PS_sum=$PS_sum+$PointsScored;
}

remains the same?

 

I don't really want to change it as it's actually as an include and also used elsewhere.

 

Surely it doesn't matter if the end result is the same? All I was saying is that running a query in a loop is a pretty inefficient way of doing things and requires you to write much more code than is necessary.

[MikEst]

Can I do it somehow that this

while(list($PlayerIDnumb, $PointsScored) = mysql_fetch_row($result)){
     $PS_sum=$PS_sum+$PointsScored;
}

remains the same?

 

I don't really want to change it as it's actually as an include and also used elsewhere.

 

Surely it doesn't matter if the end result is the same? All I was saying is that running a query in a loop is a pretty inefficient way of doing things and requires you to write much more code than is necessary.

Well, it does matter as the code I've posted here is a shortened and simplified version (same goes for input and output) and I just wanted to know if it's structurally possible to do it that way, because I really don't want to rewrite the whole thing (at least not right now).

[MikEst]

bump