code will not SUM negative number in mysql field? :( pls help!)

[tobitimac]

 

I have a code to sum up a field called quantityHand in MySQL, in my example below, the value always return 40 as a positive number instead of 40.00-

 

0.00

0.00

40.00-

 

I have used intval() or floatval() function to convert the type of variable from string to int/float but still not working. Any help please?

 

Here is the code

 

$sql = mysql_query("SELECT DISTINCT itemNumber, itemDesc, quantityHand, SUM(quantityHand) AS quantityHand FROM inventory where itemNumber like '%$term%' GROUP BY `itemNumber` ORDER BY `itemNumber`");


while ($row = mysql_fetch_array($sql)){
  	echo "<b>";
echo "</td><td style=\"text-align: center;\"><b>";
echo $row['itemNumber'];
echo "</td><td style=\"text-align: center;\"><b>";
echo $row['itemDesc'];
//echo "</td><td style=\"text-align: right;\">";
echo "</td><td style=\"text-align: center;\"><b>";
echo $row ['quantityHand'];
echo "</td></tr>";
    echo "</b>";

 

 

 

 

[wildteen88]

What data type have you set the quantityHand field to? It you have set to INT then it'll remove anything after the decimal point. If you are storing numbers such as 12.99 then you need to set the data type to FLOAT instead.

 

Or you can format $row['quantityHand'] as a float, eg

echo sprintf('%01.2f', $row['quantityHand']);

[tobitimac]

What data type have you set the quantityHand field to? It you have set to INT then it'll remove anything after the decimal point. If you are storing numbers such as 12.99 then you need to set the data type to FLOAT instead.

 

Or you can format $row['quantityHand'] as a float, eg

echo sprintf('%01.2f', $row['quantityHand']);

 

Thanks for your reply. i used VARCHAR for quantityHand in my MySQL after using echo sprintf('%01.2f', $row['quantityHand']); and getting 40.00 instead of 40.00- because in my MySQL i have 40.00-

 

Please help.

[wildteen88]

VARCHAR is for storing string data not numbers! MySQL has special data types for numbers. Either set the data type to FLOAT or INT.

[PFMaBiSmAd]

I'm pretty sure there are very few written languages and zero programming languages that use a trailing - sign on negative numbers.

 

You need to store your numbers with a leading negative sign, i.e. -40.0

[tobitimac]

I'm pretty sure there are very few written languages and zero programming languages that use a trailing - sign on negative numbers.

 

You need to store your numbers with a leading negative sign, i.e. -40.0

 

Thanks am looking into my data now i think the problem might be because i have the data as 40.00- insteade of -40.00