Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
20.9.2014 - We have just updated our course layouts and descriptions and added our 2015 schedule.

math in php / mysql

Posted by bschultz (bschultz), 23 May 2003
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

Posted by admin (Graham Ellis), 23 May 2003
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

Posted by admin (Graham Ellis), 24 May 2003
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>


Posted by bschultz (bschultz), 26 May 2003
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

Posted by admin (Graham Ellis), 26 May 2003
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.

Posted by bschultz (bschultz), 27 May 2003
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

Posted by admin (Graham Ellis), 27 May 2003
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.

Posted by bschultz (bschultz), 30 May 2003
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

Posted by admin (Graham Ellis), 30 May 2003
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!

Posted by bschultz (bschultz), 30 May 2003
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?

Posted by admin (Graham Ellis), 30 May 2003
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);


Posted by John_Moylan (jfp), 30 May 2003
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]


Posted by bschultz (bschultz), 30 May 2003
thanks all for the help...sprintf did the trick!



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho