Please help with calculations...

DeltaRho2K

Any help would be appreciated on this... I am doing this locally on WinXP using IIS4, PHP, and mySQL (latest production revs) and don't have a host that's php enabled yet.

I am trying to do some calculations from a table, and am having trouble figuring it out.
(I am fairly new to php and mySQL, and can do the basics, but am stumbling with the calculations)

OK, here's the scenario... 2 tables, bowlers and scores and their SQL is below:

CREATE TABLE bowlers (
bowlerID int(11) NOT NULL auto_increment,
abcID int(11) default NULL,
lastname varchar(25) NOT NULL default '',
firstname varchar(25) NOT NULL default '',
dateadded date default NULL,
PRIMARY KEY (bowlerID),
UNIQUE KEY abcID (abcID)
) TYPE=MyISAM COMMENT='This table holds the specific bowler information.' AUTO_INCREMENT=11 ;

CREATE TABLE scores (
scoreID int(11) NOT NULL auto_increment,
bowlerID int(11) default NULL,
datebowled date default NULL,
game1 int(3) default NULL,
game2 int(3) default NULL,
game3 int(3) default NULL,
series int(11) default NULL,
PRIMARY KEY (scoreID)
) TYPE=MyISAM AUTO_INCREMENT=21 ;

Now, the bowlerID from the bowlers table is relational to the bowlerID field in the scores table.

What I want to do is to run a query that totals game1, game2 and game3 from the scores table to create a total pinfall for a given week. I then want to add up all of the total pinfalls for each week for a given bowler (I will explain this better in a moment). Lastly, I want to count the total number of games bowled during that time frame and divide my total pinfall by the total numbre of games bowled to find an average.

In other words, say bowler#1 bowls 200, 200, 200 in week 1. I want to add that up to get the 600 for that week.

Now, say he bowls 200, 200, 200 for the next 4 weeks. I want to add all of those up to get 3000. (or 600 for each week of the 5 week span)

I want to add up a count to determine that 15 games were bowled.

I want to calculate the average (3000/15) to get 200.

I have to be able to determine if a score value is NULL, so I can factor that in (just in case someone only bowls 2 of 3 games), to the calculation doesn't count that as one of the games in the final calculation.

In other words, it would be 2800/14 instead.

I figured out this much (to count all of the games bowled) but I am sure this can be cleaned up:

$game1=mysql_query("select game1 from scores where bowlerID = $bid AND game1 is NOT NULL");
$game2=mysql_query("select game2 from scores where bowlerID = $bid AND game2 is NOT NULL");
$game3=mysql_query("select game3 from scores where bowlerID = $bid AND game3 is NOT NULL");
$countg1=mysql_num_rows($game1);
$countg2=mysql_num_rows($game2);
$countg3=mysql_num_rows($game3);
$totalcount=$countg1+$countg2+$countg3;

I figured out this much (to total weekly pinfall):

$scoretotals=mysql_query("SELECT game1, game2, game3, (game1+game2+game3) as totalscore from scores where bowlerID=$bid");

The problem is that my weekly pinfall query calculates each row, but I can't figure out how to store that variable, and then add up all of the weekly pinfalls.

I am thinking I have to use an array somehow, but can't figure it out. Any help would be greatly appreciated.

DeltaRho2K

Any help whatsoever would be appreciated... Even if it is to nudge me in the right direction... I am resourceful, and can usually figure these things out on my own, but need some direction...
Thanks in advance,
Jacob

batman

Wow! This can kill a man. You got some long, and confusing code here. (Code not confusing, it's what you want to do with it that is.) Now...I get most of it, I am just a bit confused on the whole pinfall thing. Try and sum that up a little bit better for me, and I will see if I can help you at all.

-Blake

DeltaRho2K

I know Batman, I am so sorry about that...

Basically, here is the skinny.

I want to add bowlers scores for each week. In other words, on 6/14/03, bowlerID # 1 bowled three games of 200, 200, 200.
Then on 6/21/03, bowlerID # 1 bowled three games of 190, 190, 190.

I want to calculate the weekly pinfall by adding up the three games scores for a given week, and add up the number of games bowled per week. (ie weekly pinfall for 6/14/03 was 600, with 3 games bowled and weekly pinfall for 6/21/03 was 570 with 3 games bowled)

I also want to calculate the total pinfall and games bowled for the 2 weeks combined. (1170 with 6 games bowled)

I am going to then divide the 1170 by 6 to find the average for the 6 games.

The math itself is simple math. It is the coding to determine the calculations that is tripping me up, because I can add up the rows, but not the columns. It's almost like I have to create a temporary pivot table of some sort so I can get my individual row calculations into their own columns or something.

I appreciate the offer to help Batman, and look for ward to your response(s). I included the original table SQL in my first post if you want to recreate my scenario. In the mean time, I am going to create an MS Access database (it helps me to better visualize things sometimes, and sometimes I can pick apart the SQL in it's queries to figure out the php and mySQL)

batman

I am still confused a little, but you can try this code. It should count all the games the bowler has played, the total pinfalls for the bowler, then give the average for it all.

Hope this works:

<?
//count the games
$game1=mysql_query("select game1 from scores where bowlerID = $bid AND game1 is NOT NULL");
$countg1=mysql_num_rows($game1);
if($countg1 >= '1')
{
$1 = "1";
}
else
{
$1 = "";
}

$game2=mysql_query("select game2 from scores where bowlerID = $bid AND game2 is NOT NULL");
$countg2=mysql_num_rows($game2); 
if($countg2 >= '1')
{
$2 = "1";
}
else
{
$2 = "";
}

$game3=mysql_query("select game3 from scores where bowlerID = $bid AND game3 is NOT NULL"); 
$countg3=mysql_num_rows($game3);
if($countg3 >= '1')
{
$3 = "1";
}
else
{
$3 = "";
}

//add total games played by bowler
$totalcount=$1+$2+$3;

//count the pinfall
$pin1=mysql_query("select game1 from scores where bowlerID = $bid AND game1 is NOT NULL");
while ($row1 = mysql_fetch_array($pin1, MYSQL_ASSOC))
{
foreach($row1 as $pincount1)
}

$pin2=mysql_query("select game2 from scores where bowlerID = $bid AND game2 is NOT NULL");
while ($row2 = mysql_fetch_array($pin2, MYSQL_ASSOC))
{
foreach($row2 as $pincount2)
}

$pin3=mysql_query("select game3 from scores where bowlerID = $bid AND game3 is NOT NULL");
while ($row3 = mysql_fetch_array($pin3, MYSQL_ASSOC))
{
foreach($row3 as $pincount3)
}

//add all pinfalls for bowler
$totalpins = $pincount1+$pincount2+$pincount3;


//get the average for the bowler
$average = $totalpins / $totalcount;
?>

Let me know of that helps at all,
-Blake

DeltaRho2K

Thanks Batman, what you did worked for me, but while awaiting your reply, I actually figured it out (without worrying about the IS NOT NULL stuff.)

I included the code below. Any other help would be appreciated (because obviously, if I can shortcut the code, that would be handy) As it stands though, this works, and works pretty quick.

Right now, I am coding the error correction in case there were no games bowled. (I don't want to produce some sort of "divide by zero" error in my $average calculation.)

Thanks again,
Jacob

<?PHP
	include "config.php";
	{
		// Connect to Database and Retrieve Variables
			$connect = mysql_connect($server, $db_user, $db_pass) or die ("Database CONNECT Error (rotator/line 5)");
			mysql_select_db($database);
			extract ($HTTP_GET_VARS);

	// Get Scores
		$scores = mysql_query("SELECT * FROM scores WHERE bowlerID = $bid") or die ("Unable to get scores. (Line 9)");

	// Calculate Stats from Score
		$stats=mysql_query("SELECT scores.bowlerID, Sum(scores.game1) AS Scores1, Sum(scores.game2) AS Scores2, Sum(scores.game3) AS Scores3, Count(scores.game1) AS Games1, Count(scores.game2) AS Games2, Count(scores.game3) AS Games3 FROM scores WHERE scores.bowlerID='$bid' GROUP BY scores.bowlerID") or die ("Unable to calculate scores. (Line 12)");
		$calcstats = mysql_fetch_array($stats);
		$totalscore = $calcstats["Scores1"]+$calcstats["Scores2"]+$calcstats["Scores3"];
		$totalgames = $calcstats["Games1"]+$calcstats["Games2"]+$calcstats["Games3"];
		$average = $totalscore/$totalgames;

	// Bowler Information
		$bowlerinfo=mysql_query("SELECT * FROM bowlers WHERE bowlerID = '$bid'") or die ("Unable to get bowler's information. (Line 19)");
	$bowlers = mysql_fetch_array($bowlerinfo);
}
?>

<html>
<head>
	<title><? print $sitename ?></title>
</head>

<body>

<?
	print $bowlers["lastname"];
	print ", ";
	print $bowlers["firstname"];
	print "<br>";
	print $bowlers["abcID"];
?>
<br><br>
<table>
<tr>
<td width="150"><b>Date Bowled</b></td>
<td width="70" align="center"><b>Game 1</b></td>
<td width="70" align="center"><b>Game 2</b></td>
<td width="70" align="center"><b>Game 3</b></td>
</tr>
<?

while ( $row = mysql_fetch_array($scores) )
{
	print "<tr><td valign=top>";
	print $row["datebowled"];
	print "</td>";
	print "<td align=center>";
	print $row["game1"];
	print "</td>";
	print "<td align=center>";
	print $row["game2"];
	print "</td>";
	print "<td align=center>";
	print $row["game3"];
	print "</td></tr>";		
}
?>
</table>
<ol>

<?
	print "Total Pinfall: ";
	print $totalscore;
	print "<br>Total Games: ";
	print $totalgames;
	print "<br>Average: ";

	If ($totalgames < "9"):
		print "N/A (Not enough games bowled to establish average)";
	else:
		print $average;
	endif;
?>

</body>
</html>

<?
mysql_close($connect);
?>

batman

Well...the code that you have looks perfectly fine to me. I don't really think there is any way of shortening it. Except mabey all the prints.

See if you like this better:

<?PHP
    include "config.php";
    {
        // Connect to Database and Retrieve Variables
            $connect = mysql_connect($server, $db_user, $db_pass) or die ("Database CONNECT Error (rotator/line 5)");
            mysql_select_db($database);
            extract ($HTTP_GET_VARS);

    // Get Scores
        $scores = mysql_query("SELECT * FROM scores WHERE bowlerID = $bid") or die ("Unable to get scores. (Line 9)");

    // Calculate Stats from Score
        $stats=mysql_query("SELECT scores.bowlerID, Sum(scores.game1) AS Scores1, Sum(scores.game2) AS Scores2, Sum(scores.game3) AS Scores3, Count(scores.game1) AS Games1, Count(scores.game2) AS Games2, Count(scores.game3) AS Games3 FROM scores WHERE scores.bowlerID='$bid' GROUP BY scores.bowlerID") or die ("Unable to calculate scores. (Line 12)");
        $calcstats = mysql_fetch_array($stats);
        $totalscore = $calcstats["Scores1"]+$calcstats["Scores2"]+$calcstats["Scores3"];
        $totalgames = $calcstats["Games1"]+$calcstats["Games2"]+$calcstats["Games3"];
        $average = $totalscore/$totalgames;

    // Bowler Information
        $bowlerinfo=mysql_query("SELECT * FROM bowlers WHERE bowlerID = '$bid'") or die ("Unable to get bowler's information. (Line 19)");
    $bowlers = mysql_fetch_array($bowlerinfo);
}
if($bowlers > 0)
{
$fname = $bowlers["firstname"];
$lname = $bowlers["lastname"];
$abcID = $bowlers["abcID"];
}

while($row = mysql_fetch_array($scores))
{
$date = $row["datebowled"];
$g1 = $row["game1"];
$g2 = $row["game2"];
$g3 = $row["game3"];
}

?>

<html>
<head>
    <title><? print $sitename ?></title>
</head>

<body>

<?
    echo"$lname, $fname <br> $abcID";
?>
<br><br>
<table>
<tr>
<td width="150"><b>Date Bowled</b></td>
<td width="70" align="center"><b>Game 1</b></td>
<td width="70" align="center"><b>Game 2</b></td>
<td width="70" align="center"><b>Game 3</b></td>
</tr>
<?

    echo "<tr><td valign=top>
              $date
              </td>
              <td align=center>
              $g1
              </td>
              <td align=center>
              $g2
              </td>
              <td align=center>
              $g3
              </td></tr>";

?>
</table>
<ol>

<?
    echo "Total Pinfall: $totalscore <br> Total Games: $totalgames <br> Average: ";?><? If ($totalgames < "9")
            {
            echo "N/A (Not enough games bowled to establish average)";
             }
        else{
            echo "$average";
               }
?>

</body>
</html>

<?
mysql_close($connect);
?>

-Blake

DeltaRho2K

Thanks batman, I didn't even think about the echo command on this...

Thanks for all of your input and help...

Jacob