Solved

Using commas in MySQL Decimal fields

Posted on 2007-03-19
8
1,106 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:Calvin Close
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

635 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