Page 1 of 1
Getting bad result in aritmetic operation
Posted: Mon Jun 15, 2009 8:13 pm
by fauvent
Guys Guys Guys!!! This is my first post in this great forum... I hope i can help in the future but for now i am a very newby in this programation stuff

... i hope you can help me...
I am trying to make a sumatory of 2 diferent colums in a data base... then divide this two results in one variable (yeah, i know maybe this is the most stupid question in the whole world ) The story is that im getting a good result when i'm making a sumatory or substraction with this tu variables, but when im trying to make a multiplication or division it returns me a wrong number... very close actually, but not good... here is my code...
Code: Select all
<?php
$con = mysql_connect("localhost","server_name","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("server_name", $con);
$activos = mysql_query("SELECT suma FROM ausentismo");
$total = mysql_num_rows($activos);
echo $total. " de 20";
$result = mysql_query("SELECT sum(suma / multiplicacion) as record FROM ausentismo");
if (!$result) {
die('Invalid query: ' . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
echo "<p>" . $row['record'] . "</p>";
}
?>
Thank you very much for the interest of helping people to develope programation skills and thanks for you all anticipatedly. Regards!
Re: Getting bad result in aritmetic operation
Posted: Tue Jun 16, 2009 6:33 pm
by fauvent
No one?

Re: Getting bad result in aritmetic operation
Posted: Tue Jun 16, 2009 7:16 pm
by fauvent
Im trying this way by this time...
Code: Select all
<?php
$con = mysql_connect("localhost","prodiage_arh","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("prodiage_arh", $con);
$activos = mysql_query("SELECT suma FROM ausentismo");
$total = mysql_num_rows($activos);
echo $total. " de 20";
$result = mysql_query("SELECT sum(suma) as record FROM ausentismo");
if (!$result) {
die('Invalid query: ' . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
echo "<p>" . $row['record'] . "</p>";
}
$result2 = mysql_query("SELECT sum(multiplicacion) as record2 FROM ausentismo");
if (!$result) {
die('Invalid query: ' . mysql_error());
}
while ($row = mysql_fetch_array($result2)) {
echo "<p>" . $row['record2'] . "</p>";
}
$result3 = $result / $result2;
echo "$result3";
?>
The result must be... .0502704 and the code is giving me back "0.8"... any idea please?
Re: Getting bad result in aritmetic operation
Posted: Tue Jun 16, 2009 7:34 pm
by Eric!
I don't know about the others on this board, but I'm hard pressed to debug your problem when you are pulling unknown things from a database and I can't see the data or the type of variable you're reading.
I suggest hard coding in the numbers and removing all the mysql stuff. Then if you still have the problem I or (someone here) can probably provide some kind of insight.
My first guess is you need to cast them to floating points during the math operations, but since I don't know what you're reading and I've never done math with database data (I assume they come in as strings, but I don't know for sure) I can only guess. You can put a var_dump($result) variables in there to see how php is handling the data.
Re: Getting bad result in aritmetic operation
Posted: Tue Jun 16, 2009 8:09 pm
by fauvent
First of all thank you very much for replying. Second im very sorry if im causing a problem but i am not even close to be a programer, thats why i am looking for help... i will do my best to make you guys this thing easier... i will try with var_dump($result) thank you
Re: Getting bad result in aritmetic operation
Posted: Tue Jun 16, 2009 8:24 pm
by fauvent
the data in the mysql server is a very simple example to make a test... and is the folowing...
mes • suma • multiplicacion
enero • 82 • 1600
enero • 76 • 1543
I hope it can help... regards
Re: Getting bad result in aritmetic operation
Posted: Tue Jun 16, 2009 11:30 pm
by McInfo
The mysql_query() function returns a resource. Your script is trying to perform a mathematical operation on two resources:
Code: Select all
$result = mysql_query("SELECT sum(suma) as record FROM ausentismo"); // $result is a resource
$result2 = mysql_query("SELECT sum(multiplicacion) as record2 FROM ausentismo"); // $result2 is a resource
$result3 = $result / $result2; // A mathematical operation on two resources
To get the data from the resource, use the mysql_fetch_*() functions. Also, instead of using multiple queries, combine the queries and let SQL handle the division:
Code: Select all
$query = "SELECT (SUM(suma) / SUM(multiplicacion)) AS quotient FROM ausentismo";
$result = mysql_query($query); // $result is a resource
$quotient = null;
if (is_resource($result)) {
// Only one row is expected from the query, so a loop is not needed
$row = mysql_fetch_assoc($result); // $row is an array
$quotient = $row['quotient']; // $quotient is a number as a string
}
var_dump($quotient);
PHP Manual:
mysql_query
The reason your script assigns $result3 a value of 0.8 instead of ~0.05027 is that resources are identified by numbers. $result's ID was probably 4 and $result2's ID was probably 5
4 / 5 = 0.8
You can see a resource's ID number by using var_dump():
Code: Select all
var_dump($result); // resource(4) of type (mysql result)
Edit: Added, then removed, then thought better of it and re-added the resource-ID explanation.
Edit: This post was recovered from search engine cache.
Re: Getting bad result in aritmetic operation
Posted: Wed Jun 17, 2009 12:04 am
by Eric!
Thats cool mcinfo. I didn't know mysql could do that. He's right you need to extract the values from the resource or string. Here's your code again, I think it might be more clear for you to understand.
I am not sure if the associations 'record' and 'record2' are correct. These names need to match the field structure in your table. However assuming you've done all that correctly, I think this should work.
Code: Select all
<?php
$con = mysql_connect("localhost","prodiage_arh","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("prodiage_arh", $con);
$activos = mysql_query("SELECT suma FROM ausentismo");
$total = mysql_num_rows($activos);
echo $total. " de 20";
$result = mysql_query("SELECT sum(suma) as record FROM ausentismo");
if (!$result) {
die('Invalid query: ' . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
echo "<p>" . $row['record'] . "</p>";
}
$result2 = mysql_query("SELECT sum(multiplicacion) as record2 FROM ausentismo");
if (!$result) {
die('Invalid query: ' . mysql_error());
}
while ($row2 = mysql_fetch_array($result2)) {
echo "<p>" . $row2['record2'] . "</p>";
}
$result3 = $row['record']/$row2['record2'];
echo "$result3";
?>
note i added row2 and changed how the values are extracted from the row and row2 resource.
Re: Getting bad result in aritmetic operation
Posted: Wed Jun 17, 2009 12:30 am
by McInfo
There is really no reason to use two queries to pull two sums from the same table. In fact, all three queries can be merged into one.
Code: Select all
<?php
$con = mysql_connect('localhost', 'prodiage_arh', 'password')
or die('Could not connect to the database server.');
mysql_select_db('prodiage_arh', $con)
or die('Could not connect to the database.');
$query = "
SELECT COUNT(*) AS cuenta
, SUM(suma) AS suma1
, SUM(multiplicacion) AS suma2
, (SUM(suma) / SUM(multiplicacion)) AS cociente
FROM ausentismo
";
$result = mysql_query($query, $con);
if (is_resource($result)) {
$row = mysql_fetch_assoc($result);
echo '<p>Count: '.$row['cuenta'].'</p>';
echo '<p>Sum 1: '.$row['suma1'].'</p>';
echo '<p>Sum 2: '.$row['suma2'].'</p>';
echo '<p>Quotient: '.$row['cociente'].'</p>';
} else {
echo '<p>Query failed.</p>';
}
mysql_close($con);
?>
Edit: This post was recovered from search engine cache.
Re: Getting bad result in aritmetic operation
Posted: Wed Jun 17, 2009 3:11 am
by fauvent
Re: Getting bad result in aritmetic operation
Posted: Wed Jun 17, 2009 6:53 am
by Eric!
I wondered how php was able to pull 0.8 out of the resource.
fauvent, the only mistake you really made was in how you were pulling the numbers from the mysql string:
$result3 = $result / $result2;
should have been more like:
$result3 = $row['record']/$row2['record2'];
Re: Getting bad result in aritmetic operation
Posted: Wed Jun 17, 2009 10:48 am
by McInfo
fauvent wrote:You are a very good forum team [...] if i can make a monetary contribution to your forum i will be glad to do it
This forum is just an aggregation of people from around the world who are all interested in a common subject. It is as much your forum as it is mine. I don't work here; I just came here one day and decided to share my knowledge. I keep contributing because, once in a while, people like you respond with enthusiastic thanks and praise; and that gives me a big ego boost. That is enough compensation for me, but if you are serious about monetary donations, send me a PM.
Edit: This post was recovered from search engine cache.