CODEWITHSUNDEEP
mysql
R Fauzdar
R Fauzdar

Reputation: 241

error when insert values in fields

I created a table Place in mysql. In this table there are three fields.

1. Place_Name varchar(20)
2. latitude  decimal(15,12)
3. longitude decimal(15,12)

There is a problem occurred when user inserted value like as 55636.1232 in latitude or in longitude.

Because this value is above the range of capacity of these fields. Because i allowed 3

digit before decimal point while user is trying to enter more than 3 digit before decimal

points. In this case 999.999999999999 value is inserted but i want to insert my default

value if user do such type of mistake.

I want that when user do this type of mistake then my default value should be entered in that record.

Please help me what should i do to solve such type of problem. I am waiting for your suggestions.

Thank you in advance.

Upvotes: 2

Views: 391

Answers (2)

James
James

Reputation: 3805

You can write a case statement into your mysql statement.

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

So in your case, you might write something like:

Insert Into coordinates (Place_Name,latitude,longitude) 
Values 
('$name',
Case $latitude When $latitude > 99.999 Then '$defaultLat' Else '$latitude' End Case, 
Case $longitude When $longitude > 99.999 Then '$defaultLong' Else '$longitude' End Case);

Upvotes: 0

Mahesh Patil
Mahesh Patil

Reputation: 1551

You should set SQL Mode to strict_all_tables by executing 

SET sql_mode='STRICT_ALL_TABLES';

This will prevent from entering invalid data and on violation will throw this MySQL error:

Out of range value for column 'latitude' at row 1

Upvotes: 2

Related Questions