Sum not working correctly

[Mike Solstice]

Ok...been at this for quite a while now & I've decided it's time to bow to the masters lol Please help!! Totally stumped.  Not sure if this is the right forum or not, as it's kind of both a mysql & php problem...sorry if not

 

I need to calculate the sum of numeric values in a column.

 

If I use:

 

$sql = "SELECT sum(column) AS total FROM top_train WHERE days = 365 ORDER By lvl DESC LIMIT $from,$to";
$query = $dbcon->run($sql);
while($row = mysql_fetch_array($query)){
    $result = $row['total'];
}
return $result; }

 

I get 550

 

If I use:

 

$sql = "SELECT example AS total FROM top_train WHERE days = 365 ORDER By lvl DESC LIMIT 0,200";
$query = $dbcon->run($sql);
$total1 = "0";
while($row = mysql_fetch_array($query)){
    $total1 += $row['total'];
}

 

I get 550.

 

If I:

$sql = "SELECT deaths AS total FROM top_train WHERE days = 365 ORDER By lvl DESC LIMIT 0,200";
$query = $dbcon->run($sql);
$total1 = "0";
while($row = mysql_fetch_array($query)){
    echo $row['total'] . "<br />";
}

 

I get:

2,710
4,386
120
118
306

 

Which are the correct values. I've read more pages than I care to count on how to fix this...but nothing has worked. It's currently set to VARCHAR...which I assume isn't correct but would really rather avoid changing it as this new script is an update going on about 700 identical accounts.

 

I have tried changing it to INT, TINYINT, and all the rest...the only difference was 371 instead of 550. Still not accurate?

 

Any & all help would be GREATLY appreciated...but please keep it as simple as possible, still really new to php & mysql. Not a total novice but definitely padawan learner LOL Thanks!!

[Pikachu2000]

There are commas in the values, making them non-numeric values. That aside for the moment, why don't you tell us what it is that you're trying to achieve. The field names aren't the same in any of the queries above.

[PFMaBiSmAd]

Edit: There seems to be an echo in here, here, here, here...

 

The 1000's comma separator in numbers is a human only thing to make numbers more readable, to humans. To a computer, those commas are non-numeric characters and cause the computer language to stop parsing the number at the comma.

 

To a computer you are asking it to do: 2 + 4 + 120 + 118 + 306

 

You need to remove any commas from your numbers and you also need to store your numbers using a numeric data type.

 

If you want commas in the number, do that when you display it.

[Mike Solstice]

*facepalm* Yes, it was the comma. Fixed using str_replace....so obvious...knew it had to be, just been staring at the code too long to see it lol TY!!! On a side note though, even leaving it as varchar...it works. 

[PFMaBiSmAd]

leaving it as varchar...it works.

 

^^^ It may seem that way, until you try to sort/order by the values or do any greater-then/less-than comparisons, then it won't function.

[Mike Solstice]

Ok, so if I just simply alter the column to be INT instead of VARCHAR, it drops the numbers after the comma...which isn't going to work.

 

I have a script that cycles through a mysql dump line by line & then writes it to the database, and could use str_replace there to remove the commas in question...but how do I keep it from removing the commas in the dump that are supposed to be there?

[Mike Solstice]

Nevermind, was over-complicating it. Got it 

 

UPDATE table SET field_name = replace( replace(field_name, ',', ''), '"', '' );

[Mike Solstice]

Ok, that works if I run it CLI or though phpMyAdmin, but won't if I try it in php

 

$remcomma = "UPDATE top_train SET deaths = replace( replace(field_name, ',', ''), '"', '' )";
mysql_query($remcomma);

 

I assume because of the quotes used in the regex. I tried escaping the "

 

$remcomma = "UPDATE top_train SET deaths = replace( replace(field_name, ',', ''), '\"', '' )";
mysql_query($remcomma);

 

Which broke it altogether. Anyone have any ideas?

 

Thanks!

[PFMaBiSmAd]

Do you actually have double-quotes " stored in the field?

 

This should be all that you need -

$remcomma = "UPDATE top_train SET deaths = replace(deaths, ',', '')";

 

 

Edit: I'm assuming that the column where the numbers are at with the commas is named deaths and that is where you want to put the numbers back without the commas?