Getting bad result in aritmetic operation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fauvent
Forum Newbie
Posts: 6
Joined: Mon Jun 15, 2009 8:08 pm

Getting bad result in aritmetic operation

Post 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 :P :oops:... 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!
fauvent
Forum Newbie
Posts: 6
Joined: Mon Jun 15, 2009 8:08 pm

Re: Getting bad result in aritmetic operation

Post by fauvent »

No one? :(
fauvent
Forum Newbie
Posts: 6
Joined: Mon Jun 15, 2009 8:08 pm

Re: Getting bad result in aritmetic operation

Post 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?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Getting bad result in aritmetic operation

Post 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.
fauvent
Forum Newbie
Posts: 6
Joined: Mon Jun 15, 2009 8:08 pm

Re: Getting bad result in aritmetic operation

Post 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
fauvent
Forum Newbie
Posts: 6
Joined: Mon Jun 15, 2009 8:08 pm

Re: Getting bad result in aritmetic operation

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Getting bad result in aritmetic operation

Post 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.
Last edited by McInfo on Tue Jun 15, 2010 11:12 pm, edited 1 time in total.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Getting bad result in aritmetic operation

Post 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.
Last edited by Eric! on Wed Jun 17, 2009 6:40 am, edited 1 time in total.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Getting bad result in aritmetic operation

Post 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.
Last edited by McInfo on Tue Jun 15, 2010 11:12 pm, edited 1 time in total.
fauvent
Forum Newbie
Posts: 6
Joined: Mon Jun 15, 2009 8:08 pm

Re: Getting bad result in aritmetic operation

Post by fauvent »

McInfo!!! Woooow LOL i cant belive i was so stupid... well actually im not... i just dont know the php funtions... and my God you are really genius!!! you really impresed me!!! You are a very good forum team :) i hope i can help in the future or if i can make a monetary contribution to your forum i will be glad to do it :) thank you very much... I will be posting often if you dont mind that "I DONT KNOW ALMOST NOTHING" :P

REGARDS!!! :drunk: :drunk: :drunk: :drunk: :drunk: :drunk:
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Getting bad result in aritmetic operation

Post 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'];
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Getting bad result in aritmetic operation

Post 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.
Post Reply