I'm trying to understand / learn some math in php and mysql.  The following code returns the wrong answer.  Can someone tell me why?

Code:

<? $DBhost = "localhost"; $DBuser = "my-user-name"; $DBpass = "my-pasword"; $DBName = "my-db"; $table = "my-table"; mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); @mysql_select_db("$DBName") or die("Unable to select database $DBName"); echo $T1+$T2; ?>

In the DB,

T1=1
T2=2

So, the script should output "3" as the answer.  Instead, it reads "0".

What am I doing wrong?

Thanks,

Brian

You're connecting to the database but not performing any queries - so you're not reading anything back / putting anything into PHP variables.   I'll post an example in the next 24 hours

Here's a worked example that picks up all the rows from a table called fred in the test database and sums the second (age) column.  Reported result is "66":

Code:

Testing <br><table border=1> <?php $DBhost = "localhost"; $DBuser = "learner"; $DBpass = ""; $DBName = "test"; $table = "fred"; $sum = 0; mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); mysql_select_db("$DBName") or die("Unable to select database $DBName"); $result = mysql_query("SELECT * FROM $table"); while ($row = mysql_fetch_row($result)) {    print ("<tr><td>$row[0]</td><td>$row[1]</td></tr>");    $sum += $row[1];    } ?> </table> <?php print "Total is $sum"; ?>

Here's the table via the mysql client:

Code:

mysql> select * from fred; +----------------+------+ | name           | age  | +----------------+------+ | Graham Ellis   |   48 | | Kimberly Ellis |   18 | +----------------+------+ 2 rows in set (0.00 sec) mysql>

Graham,

Thanks so much for the help...but I'd like to take this one step farther.  In each table, I have a cell called "T1"...can I add all tables value for "T1"...meaning that it would add up all "T1" cells in all tables?

In other words, "SELECT FROM TABLE 1, TABLE 2, TABLE 3...ETC."?


Brian

You'll probably need a series (in a loop?) of select statements - the format you suggested in the SQL would do a table join which I don't think is what you want at all.   You want to do a sum of all the values in the T1 column across all rows of a number of tables, right?

By the way - rather than read back individual rows from a table, you might like to look at the SQL sum function if the only thing you wanty back is that total.

Graham,

Yes, all I want is the value of all "T1" cells...but in some cases, I'll need that added, and then multiplied by something, or divided by something else.

Brian

Sounds like it's best to get the T1 total out through the sum function, then do the rest of the maths in the PHP.

Here's what I've come up with to get the sum of all "T1" cells...and it works:

Code:

<?php $DBhost = ""; $DBuser = ""; $DBpass = ""; $DBName = ""; mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); mysql_select_db("$DBName") or die("Unable to select database $DBName"); $tablenames = array("game1", "game2", "game3", "game4", "game5", "game6", "game7", "game8", "game9", "game10", "game11", "game12", "game13", "game14", "game15", "game16", "game17", "game18", "game19", "game20", "game21", "game22", "game23", "game24", "game25", "game26", "game27", "game28", "game29", "game30" ); // (fill in the rest of the table names) $column_name = "T1"; // column you want the database-wide sum of $overall_sum = 0; foreach ($tablenames as $table) { $query = "SELECT SUM(".$column_name.") as sum FROM ".$table." "; $result_array = mysql_fetch_array(mysql_query($query)); $overall_sum += $result_array[sum]; } echo "$overall_sum"; ?>

but I can't figure out how to take the sum of all T1's and divide it by the sum of all T2's.

Any thoughts?  Thanks in advance!

Brian

You could add an extra loop to sum all the columns you want ...

Code:

$colswant = ("T1","T2"); foreach ($colswant as $column_name) { $overall_sum[$column_name] = 0; foreach ($tablenames as $table) { $query = "SELECT SUM(".$column_name.") as sum FROM ".$table." "; $result_array = mysql_fetch_array(mysql_query($query)); $overall_sum[$column_name] += $result_array[sum]; } }

then divide one result by the other

Code:

$result_i_want = $overall_sum["T1"] / $overall_sum["T2"]

Knowing that you're logging match stats, beware of the "early season" problem .... If you run my sample code when you don't yet have a single piece of data in your tables, you'll end up trying to divide by zero - do a check first if you clear your tables out annually!

I actually got it with this:

Code:

$column_name1 = "T64"; // first column you want the database-wide sum of $column_name2 = "T62"; // second column you want the database-wide sum of $overall_sum1 = 0; $overall_sum2 = 0; foreach ($tablenames as $table) { $query = "SELECT SUM(".$column_name1.") as sum1, SUM(".$column_name2.") as sum2 FROM ".$table." "; $result_array = mysql_fetch_array(mysql_query($query)); $overall_sum1 += $result_array[sum1]; $overall_sum2 += $result_array[sum2]; } // <- end the for(), and since I did both variables in the one loop, I'm done querying // output $divided = $overall_sum1 / $overall_sum2; echo "$divided";

but I can't seem to find how to round it off...I'm looking at the php round function now in the manual, but there is some feedback that says that it doesn't work right all the time.  I want some rounded to the 2nd decimal pont...and some rounded to the third decimal point.  Is there another function that will do this reliably?

You're looking for sprintf - described in full at http://www.php.net/manual/en/function.sprintf.php.

replace

Code:

$divided = $overall_sum1 / $overall_sum2;

by

Code:

$divided = sprintf("%.2f", $overall_sum1 / $overall_sum2);

Now I'm not 100% on this one (and I should really check before posting....but I won't )

But! I'd be very suprised if there wasn't  "round, ceiling, floor" functions in mysql to do the math for you.

In general if a database supplys a function, you should use it instead of getting your programming language do it as the DB will be genrally quicker. This is certainly true in sorting, and is probably true of math functions too.

Perhaps I'm waffling, I'm sure someone will tell me if I am.

jfp

[Another glance at the question says to me that you want to do in PHP...so hey...I'm waffling]


thanks all for the help...sprintf did the trick!

---