Solved

Using commas in MySQL Decimal fields

Posted on 2007-03-19
8
1,086 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
Comment Utility
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
Comment Utility
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
Comment Utility
for example

$value = '2,118.56';
$value = str_replace(',', '', $value);
echo $value;
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 14

Expert Comment

by:Aamir Saeed
Comment Utility
also try using this in query

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

Expert Comment

by:Raynard7
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now