CODEWITHSUNDEEP
phpmysqlphpmyadminnumeric
Ben
Ben

Reputation: 1349

How can I prevent MySQL numeric values from changing? (phpMyAdmin)

I've created the table INVOICES in MySQL on phpMyAdmin.

CREATE TABLE INVOICES(
 INVOICENUMBER       NUMERIC(3) NOT NULL PRIMARY KEY,
 JOBID               NUMERIC(3) NOT NULL,
 CURRENTDATE         DATE,
 CHARGEDETAILS       VARCHAR(200),
 CHARGESUBTOTAL      NUMERIC(3,2) NOT NULL CHECK (CHARGESUBTOTAL >= 0),
 GRANDTOTAL          NUMERIC(3,2) NOT NULL CHECK (GRANDTOTAL >= 0),
 FINALWORKLOG        VARCHAR(200),
 TERMS               VARCHAR(200),
 INDEX (JOBID)
 );

However, I've encountered a problem when trying to add records to the table. The first insert command enters everything properly, but the second command converts numeric values (CHARGESUBTOTAL and GRANDTOTAL) to 9.99 in the table. I'm not sure what's causing it, so can anyone help me out?

INSERT INTO INVOICES VALUES (1,1,'2014-11-15','None as the job was cancelled.',0.00,0.00,'The job was cancelled.','None.');
INSERT INTO INVOICES VALUES (2,2,'2014-11-15','£31.80 for total work.',31.80,35.00,'Employee took care of all the expected duties.','Payment should be undertaken within 7 days of receipt.');

Upvotes: 0

Views: 586

Answers (5)

Pankaj
Pankaj

Reputation: 581

Issue is with NUMERIC(3,2). Change this with NUMERIC(5,2) to gets things working.

Explanation :

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2) In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

Ref Link : http://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html

Upvotes: 1

user3522371
user3522371

Reputation:

Change this:

 CHARGESUBTOTAL      NUMERIC(3,2) NOT NULL CHECK (CHARGESUBTOTAL >= 0),
 GRANDTOTAL          NUMERIC(3,2) NOT NULL CHECK (GRANDTOTAL >= 0),

To:

 CHARGESUBTOTAL      NUMERIC(4,2) NOT NULL CHECK (CHARGESUBTOTAL >= 0),
 GRANDTOTAL          NUMERIC(4,2) NOT NULL CHECK (GRANDTOTAL >= 0),

Documentation.

Upvotes: 1

Dai
Dai

Reputation: 155578

The problem is the column data-type: NUMERIC(3,2).

See here: http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html

The 3 means 3 significant digits will be stored (i.e. 0-999 or 0-99.9 or 0-9.99). 2 is the number of digits (from the s.f. number) after the radix point, so 3,2 means 0-9.99 but 4,1 means 0-99.99, and so on.

Change it to NUMERIC(10,2) or something similar according to your business needs.

Upvotes: 1

juergen d
juergen d

Reputation: 204894

Because the values (i.e. 31.80) are out of range and the max value is stored in the column which is 9.99.

NUMERIC(3,2)

can have 3 digits - one before and two after the comma.

Upvotes: 2

sectus
sectus

Reputation: 15464

NUMERIC(3,2) means - 3 digits for whole number and 2 after decimal point.

http://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html

Upvotes: 6

Related Questions