Data type to use for prices with commas

Applications Web Development
1

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

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

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

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

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.

$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,

6

What locale are you using?

7

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
8

Sorry but none of that information was relevant

What version of PHP are you using? If you are unsure, use the following script to display it.

<?php
echo 'Version: ' . phpversion();
?>

What country are you in?
What country is your server in?

9

Hi fpmurphy again,

Using your script I get this
Version: 5.2.14.

I`m Central America, my webhosting server is in USA.

My problem is, which part of code within products.inc.php manage the display of prices in front end, in order to try to modify to show commas in the prices.

Thanks so far for your help.

Best regards

10

Good. I had a feeling that something like this might have been the case.

Now, I need to know which country in Central America (Honduras?) and then I will provide a simple localized PHP test script for you to try.

11

Hi fpmurphy,

I have a doubt, I`m not sure why and how the country location actsworks in this particular problem of display prices with commas in front end:p, I was thinking is only modify a little bit the the risponsable functions to display the prices within the file Products.inc.php after all operations of comparison prices(hight to low, low to high) have been done by other functions. Yes I`m currently in Honduras C.A, I`ll try the code you say me, but I want to show the prices in US $, not in locale currency:D.

Thanks again for yor help.