[RESOLVED] Help with nested While statement

[robkir]

I am totally stuck and out of ideas on what seems like a really easy question which, in a nut shell is how can I get two nested while statements to work right? Sorry for the long explanation but I wasn't sure how much info anybody would need to help me solve this.

I have two tables: "Pools" and "Matches"

The Pools table has one field, the pool name and it contain elements such as A1, A2, A3, B1 for a total of say 12 items.

The Teams table has two fields, "pool name" and "points" for each individual game and a team would have three games listed in the table. A typical Teams record would look like this: "A1, 6" or "B2 , 5" (the actual contents are much more robust, but I am simplifying for the question)

I created two arrays: Pools and Teams. The Pools are in alphabetical order, Teams are not. Using two nested While statements I want to add up the total points for every instance of an individual Teams' points.

For example in the Pools table "A1" is the first record. In the Teams table the first record "A1" with 6 points. That team "A1" appears again as fifth record (A1 with 2 points), and tenth record (A1 with 3 points). The first While (with Pools A1) loops through all the Teams records and A1 ends up with a total of 11 points. The Pools changes to the next element: "A2" and reexamine all the Teams records...etc.

The problem? The While loops aren't working right and after a day of futzing (is that a word) I am out of ideas.

Here's the basic code I am using.

//---| WHILE STATEMENT for pools

while ($pc < 12) {   //Loop Name: Get Pool Name Each pool needs to be compared to every game 
print "Pool value in first WHILE is " . $pool[$pc] . "<br/>"; //debug

//---| WHILE STATEMENT for teams

while ($mc < 18) { // LOOP NAME Get Match Results this while statement examines every game and compares it to each pool
print " value of MC is  $mc <br/>" ;	 //debug

if($pool[$pc] == $homeTeam[$mc])
	$poolTeamPoints[$pc] = $poolTeamPoints[$pc] + $homeTeamPoints[$mc];

				++$mc; 	// increment Team counter to look at same pool, but next Team 


	} // end of while  Get team Results			
		++$pc; // increment Pool counter to look at next pool
		$mc = 1; 	// set back to the first record, so we can look at all the records again

		} // end of while Get Pool Name

[djjjozsi]

i have a table called teams, and i have a table called scores.
teams (teamID , name)
1 a
2 b
3 c

then i store the teams as its primary keys in the scores table.
scores table: scoreid , teamID , point
1 1 33
2 1 44
3 1 33
4 2 87
5 2 34
6 2 23

i can select their scores in one simple query:

select teams.name , sum( scores.point ) from teams LEFT JOIN scores ON scores.teamID=teams.teamID
GROUP BY teams.teamID
ORDER BY teams.name

Any question?

[robkir]

Hi djjjozsi,

I think this will work--thanks. I am putting together the framework to test it.

May I ask another, different question?

As part of this program two teams that have a zero-zero game each get 3 points. The problem is not all the scores are put in at one time. When I UPDATE the scores table it gives 3 points to teams that haven't played their games yet, because it thinks its a zero-zero tie even though the field is empty. I've tried using isset and empty. Can you point me in a direction?

I appreciate your help.

robkir

[djjjozsi]

if there is no results yet, use a field which allowes NULL values.
On update or in insert, if you want to set the match's result if the posted value is not set ( 0 is a value! ), then set NULL.

for example:

        $f_field1=isset($_POST["f_field1"])? "'". $mysql->mres($_POST["f_field1"])."'":NULL;
        $sql="INSERT INTO  table (`field1`) VALUES ($f_field1) "; 

[robkir]

That works!!!

Thanks. You saved me from endless hours of work.

robkir

[djjjozsi]

Well, Your welcome.