Solved

Using commas in MySQL Decimal fields

Posted on 2007-03-19
8
1,095 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

828 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