Solved

Using commas in MySQL Decimal fields

Posted on 2007-03-19
8
1,100 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
[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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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 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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

710 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