Login or Register to Ask a Question and Join Our Community


Web Development

Data type to use for prices with commas

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Web Development Data type to use for prices with commas
# 1  
Old 11-05-2010
Data type to use for prices with commas
Hi everybody,

I`m very new with PHP and Databases and I having the follow issue with prices data..

The original information is in CSV files.
The prices have formatted with commas and dots as follow:

12,300.99 -->(thousands separated by commas)
3,500.25 -->(thousands separated by commas)
235.50
etc

My problem is that the prices data is incorrectly stored in MySQL DB when is greater than 999.99. If a price has a comma only is stored the first number as follow:

In CSV file MySQL DB stores
12,300.99----->1.00-->Incorrect
3,500.25------>3.00-->Incorrect
235.50-------->235.50-->Correct

The structure of proPrice Field is:
Field Type Collation Attributes Null Default
prodPrice decimal(10,2) Yes NULL

Which data Type I have to use in order to see the correct price loaded within my DB?

Thanks in advance for any suggestion.
# 2  
Old 11-05-2010
Make sure your cells format fits the data format of your structure
Note that there are some DecimalSeparator in windows that may be considered depending on how MySQL does handle the conversion (or maybe MySql Does have its own config file?)
Search google with keyword : decimal separator and mysql data export import.
# 3  
Old 11-05-2010
You could also simply remove all commas from prices before adding them to the database. They serve no function to the machine, they're only present for human readability.
# 4  
Old 11-05-2010
The database should not contain thousands separators. All such formatting should be done on output according to locale settings.

Probably the best approach is to strip the thousands separator from the CSV file prior to loading the date into the database.
# 5  
Old 11-05-2010
Hi all,

After seeing your suggestions and testing change types for ProdPrice filed, I guess the best solution is to remove commas
from CSV.txt files before to be loaded into MySQL DB. The prices now appear in the website, even though without commas
like is the normal situation when we refer to money quantities.

The thing is that I would like to show prices with commas but the PHP application that runs my website has a lot of PHP scripts,
I almost sure that the related script that manage the product prices is products.inc.php and within it I assume that the last
commands (box_content) are risponsables to do the format showed in the final presentation in the website.

These commands are shown below but I have no idea how to assing to modify these commands in order to see commas in prices
in the website.

PHP Code:
$box_content->assign("PRICEREFLINKDATA",$installDir['value'].'index.php?ps='.str_replace(" ", "+", strip_tags($_GET['ps'])).$pscatvar.'&pslow='.$lowprice.'&pshigh='.ceil($lowprice + $divideprice).$rshow);                                                                                                                                                                                                          
$box_content->assign("PRICEREFINE",$row_product['prodCurrency'].$lowprice.' - '.$row_product['prodCurrency'].ceil($lowprice + $divideprice));                                                                                                                                                                                                                                                                         
$box_content->parse("prod.refine.priceli");                                                                                                                                                                                                                                                                                                                                                                           
$box_content->assign("PRICEREFLINKDATA",$installDir['value'].'index.php?ps='.str_replace(" ", "+", strip_tags($_GET['ps'])).$pscatvar.'&pslow='.ceil($lowprice + $divideprice).'&pshigh='.ceil($lowprice + ($divideprice * 2)).$rshow);                                                                                                                                                                               
$box_content->assign("PRICEREFINE",$row_product['prodCurrency'].ceil($lowprice + $divideprice).' - '.$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 2)));                                                                                                                                                                                                                                              
$box_content->parse("prod.refine.priceli");                                                                                                                                                                                                                                                                                                                                                                           
$box_content->assign("PRICEREFLINKDATA",$installDir['value'].'index.php?ps='.str_replace(" ", "+", strip_tags($_GET['ps'])).$pscatvar.'&pslow='.ceil($lowprice + ($divideprice * 2)).'&pshigh='.ceil($lowprice + ($divideprice * 3)).$rshow);                                                                                                                                                                         
$box_content->assign("PRICEREFINE",$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 2)).' - '.$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 3)));                                                                                                                                                                                                                                        
$box_content->parse("prod.refine.priceli");                                                                                                                                                                                                                                                                                                                                                                           
$box_content->assign("PRICEREFLINKDATA",$installDir['value'].'index.php?ps='.str_replace(" ", "+", strip_tags($_GET['ps'])).$pscatvar.'&pslow='.ceil($lowprice + ($divideprice * 3)).'&pshigh='.ceil($lowprice + ($divideprice * 4)).$rshow);                                                                                                                                                                         
$box_content->assign("PRICEREFINE",$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 3)).' - '.$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 4)));                                                                                                                                                                                                                                        
$box_content->parse("prod.refine.priceli");                                                                                                                                                                                                                                                                                                                                                                           
$box_content->assign("PRICEREFLINKDATA",$installDir['value'].'index.php?ps='.str_replace(" ", "+", strip_tags($_GET['ps'])).$pscatvar.'&pslow='.ceil($lowprice + ($divideprice * 4)).'&pshigh='.ceil($lowprice + ($divideprice * 5)).$rshow);                                                                                                                                                                         
$box_content->assign("PRICEREFINE",$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 4)).' - '.$row_product['prodCurrency'].ceil($lowprice + ($divideprice * 5)));                                                                                                                                                                                                                                        
$box_content->parse("prod.refine.priceli"); 
Thanks for your help so far and any help regarding this last question.

Regards,
Last edited by cgkmal; 11-05-2010 at 05:33 PM..
# 6  
Old 11-05-2010
What locale are you using?
# 7  
Old 11-05-2010
Hi fpmurpfhy,

I`m not sure if locale is the php, or MySQL version, if it is that the data visible in phpMyAdmin is as follow:

MySQL

  • Server: Localhost via UNIX socket
  • Server version: 5.1.50
  • Protocol version: 10
  • MySQL charset: UTF-8 Unicode (utf8)
Web server

  • cpaneld
  • MySQL client version: 5.1.50
  • PHP extension: mysql
phpMyAdmin

  • Version information: 3.2.4
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Is there a way to handle commas inside the data when generating a csv file from shell script?

I am extracting data via sql query and some of the data has commas. Output File must be csv and I cannot update the data in the db (as it is used by other application). Example table FavoriteThings Person VARCHAR2(25), Favorite VARCHAR2(100) Sample Data Greta rain drop on... (12 Replies)
Discussion started by: patk625
12 Replies

2. Programming

Incompatible data type fpos_t in C

This is from a program I wrote over in 1998 that I am trying to compile on a linux machine: void write_line (FILE *fp, int rec_no, line_rec *arec) { fpos_t woffset; woffset = (rec_no - 1) * sizeof(line_rec); fsetpos(fp,&woffset); fwrite(arec,sizeof(line_rec),1,fp); }On the line... (2 Replies)
Discussion started by: wbport
2 Replies

3. Shell Programming and Scripting

replace but skip data between certain commas

OK, I am one needy dude. However, how can I make the program NOT change any of the values BETWEEN the first and second "," ? I dont want any of the numbers changed that are preceded by "AT". I want ALL other numeric values > 300 changed to 300. cat qin.csv |head... (4 Replies)
Discussion started by: herot
4 Replies

4. Programming

help with data type sizes

i'm using a C program and running it on a linux server, i got 2 adressess of 2 variables, and 2 addresses of 2 chars, and compared it. and got the size of a int and the size of a char. why is a size of a int (4 bytes) bigger then the size of a char (1 byte)? also if i do &a-&b i get 1, but if i... (30 Replies)
Discussion started by: omega666
30 Replies

5. Shell Programming and Scripting

how to handle , in data where separator also commas in awk script

TEST_HEME,"SubNetwork=ONRM_RootMoR,SubNetwork=ARNC1",CELL when I split by FS="," then $0=TEST_HEME $1="SubNetwork=ONRM_RootMoR $2=SubNetwork=ARNC1" but I need this will be single value "SubNetwork=ONRM_RootMoR,SubNetwork=ARNC1" (4 Replies)
Discussion started by: Hemendra
4 Replies

6. UNIX for Dummies Questions & Answers

Inserting commas and replacing backslashes with commas

Hi, Newbie here. I have a file that consists of data that I want to convert to a csv file. For example: Jul 20 2008 1111 / visit home / BlackBerry8830/4.2.2 Profile/MIDP-2.0 Configuration/CLOC-1.1 VendorID/105 Jul 21 2008 22222 / add friend / BlackBerry8830/4.2.2 Profile/MIDP-2.0... (3 Replies)
Discussion started by: kangaroo
3 Replies

7. Shell Programming and Scripting

Perl data type checking

I am using perl 5.8.0. I need to check some values to see it they are floats. Our system does not have Data::Types so I can't use is_float. Is there something else that I can use? The only thing in Data is Dump.pm. I am not allowed to download anything to our system so I have to use what I have.... (3 Replies)
Discussion started by: ajgwin
3 Replies

8. Programming

data type limitation

I am writing some code to do analysis on the file system (HP-UX 11.11). I am using stat(..) to get file information. My problem is that the file-size may exceed the data types defined in 'sys/stat.h' & 'sys/types.h' respectively. Thus file-sizes in the Giga-byte range are not read correctly.... (2 Replies)
Discussion started by: ALTRUNVRSOFLN
2 Replies

9. Programming

time_t data type-- what does start +1 mean?

Hi, I am trying to understand an very old C program. .... time_t start, end; ptr = localtime(&start); ... fprintf(out, "%-35s 01 %5s %2s %10d 1 5 /tty/M%d/%02d %24s", buffer3, job, ver, start, mach_num,atoi(buffer), asctime(ptr)); fprintf(out, "%-35s 03 %5s %2s %10d 1 5... (9 Replies)
Discussion started by: whatisthis
9 Replies

10. Programming

FILE data type

Hi all, Can anyone tell me a little about the datatype FILE, which represents stream. What does its structure look like, and in which header file is it defined and so on... Ex : FILE *fp ; fp = fopen("filename", "w") ; (6 Replies)
Discussion started by: milhan
6 Replies
Login or Register to Ask a Question