Solved

Using commas in MySQL Decimal fields

Posted on 2007-03-19
8
1,092 Views
Last Modified: 2013-12-13
I am having problems with entering a price into a MySQL decimal (10,2) field.  If a person enters 2118.56 in the HTML form, the record enters OK.  However, if they enter 2,118.56, the price field in the record is 2.00.

I am using PHP to insert the record.

Should I be using addslashes, or something else?

Thanks,

Calvin
0
Comment
Question by:calvinclose
  • 4
  • 2
8 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 250 total points
ID: 18752051
If you are using php then you would need to use preg_replace() in php to remove all characters that are not equal to 0-9 or . or -
http://au.php.net/manual/en/function.preg-replace.php

mysql's fields will only accept plain numbers - commas are not acceptable in a numeric type
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18752058
you can strip of comma first and then enter into the DB
0
 
LVL 14

Assisted Solution

by:Aamir Saeed
Aamir Saeed earned 250 total points
ID: 18752143
for example

$value = '2,118.56';
$value = str_replace(',', '', $value);
echo $value;
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18752182
also try using this in query

   REPLACE(field, ',' , '')
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 18752185
i_m_aamir I agree, but if the user then does something different, like spaces in the number then you would need to do more str_replaces, if you use a regular expression then it will remove everything that is incorrect and you will not get this problem,

alternatively you could just test to see if the number fits the correct format - ie ########.## and if not then just post an error and get the user to correct it themselves.
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18752205
you might try something like

$my_text="This is my text, there is a comma,\rblah blah\nblah blah\ttabbed in here\tand here\n";

function replace_chars($input)
{
      $filter=array(",","\r","\n","\t");
     
      return str_replace($filter,"",$input);
}

print replace_chars($my_text);
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question