math in php / mysql
Posted by bschultz (bschultz), 23 May 2003I'm trying to understand / learn some math in php and mysql. The following code returns the wrong answer. Can someone tell me why?
In the DB,
So, the script should output "3" as the answer. Instead, it reads "0".
What am I doing wrong?
Posted by admin (Graham Ellis), 23 May 2003You'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 2003Here'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":
Here's the table via the mysql client:
Posted by bschultz (bschultz), 26 May 2003Graham,
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."?
Posted by admin (Graham Ellis), 26 May 2003You'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 2003Graham,
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.
Posted by admin (Graham Ellis), 27 May 2003Sounds 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 2003Here's what I've come up with to get the sum of all "T1" cells...and it works:
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!
Posted by admin (Graham Ellis), 30 May 2003You could add an extra loop to sum all the columns you want ...
then divide one result by the other
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 2003I actually got it with this:
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 2003You're looking for sprintf - described in full at http://www.php.net/manual/en/function.sprintf.php.
Posted by John_Moylan (jfp), 30 May 2003Now 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.
[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 2003thanks all for the help...sprintf did the trick!
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: firstname.lastname@example.org • WEB: http://www.wellho.net • SKYPE: wellho