( 3000 - 3300 ) = large numbers

homer.favenir · #1 (**permalink**) 02-15-09, 22:20

hi,
can anyone please tell me what can i do with this problem.
i have table in my localhost
and a very simple query

Code:

SELECT
datesold
, ( auctionprice - principal ) as 'Net'
, auctionprice
, principal
FROM sold_tbl where datesold = '2009-01-31'

it results just fine and what i really need.
it also output the negative number when smaller substracts to a larger number.
it is just in my localhost
but when i try this query in my webhost's phpmyadmin
it gives me wrong result when smaller substracts to a larger number.
it gives me very large number and not negative number.
e.g.
3000-3300 = -300 ( in my localhost )
3000-3300 = 18446744073709551316 ( in my webhost )

they all have the same table structure.
what seems to be the problem here?

pls, thanks in advance

r937 · #2 (**permalink**) 02-15-09, 22:52

please do a SHOW CREATE TABLE for each of your tables

i'll bet that you have declared one of the columns as UNSIGNED

homer.favenir · #3 (**permalink**) 02-15-09, 22:57

Code:

CREATE TABLE IF NOT EXISTS `sold_tbl` (
  `idsold_tbl` int(10) unsigned NOT NULL auto_increment,
  `idpawnshop_tbl` varchar(45) collate latin1_general_ci default NULL,
  `DateLoan` datetime default NULL,
  `LastName` varchar(45) collate latin1_general_ci default NULL,
  `FirstName` varchar(45) collate latin1_general_ci default NULL,
  `Address` text collate latin1_general_ci,
  `Principal` int(10) unsigned default NULL,
  `AmountWords` text collate latin1_general_ci,
  `ItemType` varchar(45) collate latin1_general_ci default NULL,
  `NetProceeds` int(10) unsigned default NULL,
  `Interest` float default NULL,
  `ServiceCharge` float default NULL,
  `DST` int(10) unsigned default NULL,
  `Description` text collate latin1_general_ci,
  `Note` text collate latin1_general_ci,
  `AuthoRep` varchar(45) collate latin1_general_ci default NULL,
  `MaturityDate` date default NULL,
  `ExpiryDate` date default NULL,
  `ServiceType` varchar(45) collate latin1_general_ci default NULL,
  `Branch` varchar(45) collate latin1_general_ci default NULL,
  `Grams` float unsigned default NULL,
  `Karats` varchar(45) collate latin1_general_ci default NULL,
  `Quantity` int(10) unsigned default NULL,
  `AuctionPrice` int(10) unsigned default NULL,
  `DateSold` date default NULL,
  `SoldTo` varchar(45) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`idsold_tbl`),
  KEY `Index_2` (`DateLoan`,`LastName`,`ItemType`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=269 ;

guelphdad · #4 (**permalink**) 02-16-09, 01:39

two things

1) you don't have auction price or principal in this table

2) your problem stems from your use of FLOAT as your column type in the fields you have here and likely in the other table (if there is one).

FLOAT is used for very large or very small numbers and should not be used as you have when you should most likely be using DECIMAL. DECIMAL is not subject to the same rounding errors.

homer.favenir · #5 (**permalink**) 02-16-09, 02:20

thanks for the help.
i made a View Table
thats why the fields that i present here is not available on the table
well i remove the unsigned in the Principal field
and also in AuctionPrice field
and it works fine now.

thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech