[SOLVED] comma screwing up calculations over 1,000

[jakebur01]

My script is working good only it screws up the numbers over 1,000. Should I not be removing the comma before I multiply?

    $targetChars=array('"', '$', ',', '*');

     $dealercheck=$row["$dealer"];
   $dealercheck=str_replace($targetChars, "", $dealercheck);
      $dealercheck = number_format($dealercheck, 2, '.', '');
  
        $distributorcheck=$row["$distributor"];
   $distributorcheck=str_replace($targetChars, "", $distributorcheck);
      $distributorcheck = number_format($distributorcheck, 2, '.', '');
  
    $listprice=$row["$list"];
   $listprice=str_replace($targetChars, "", $listprice);
   $listprice = number_format($listprice, 2, '.', '');
   
    $distributor1=$row["$distributor"];
   $distributor1=str_replace($targetChars, "", $distributor1);
   $cost=$distributor1*.95;
      $cost = number_format($cost, 2, '.', '');
  
   $level1=$row["$dealer"];
   $level1=str_replace($targetChars, "", $level1);
      $level1 = number_format($level1, 2, '.', '');
  
    $level2=$row["$dealer"];
   $level2=str_replace($targetChars, "", $level2);
   $level2=$level2*.96;
      $level2 = number_format($level2, 2, '.', '');
  
    $level3=$row["$dealer"];
   $level3=str_replace($targetChars, "", $level3);
   $level3=$level3*.92;
      $level3 = number_format($level3, 2, '.', '');
  
   $level4=$row["$dealer"];
   $level4=str_replace($targetChars, "", $level4);
   $level4=$level4*.9;
      $level4 = number_format($level4, 2, '.', '');
  
    $level5=$row["$dealer"];
   $level5=str_replace($targetChars, "", $level5);
   $level5=$level5*.88;
      $level5 = number_format($level5, 2, '.', '');
  
      $level6=$row["$dealer"];
   $level6=str_replace($targetChars, "", $level6);
   $level6=$level6*.85;
      $level6 = number_format($level6, 2, '.', '');
  

 

 

[Mchl]

You most certainly should. Use number_format only for outputting data. When performing calculations do them on raw numbers.

[samshel]

yes u should...simple way

 

ereg_replace("," , "", "1,000");

 

TIP: never use/store number with thousad seperators in DATABASE or doing operations. Thousand seperators should be used only for displaying...

[jakebur01]

What could be causing my numbers to mess up then? The fields were originally formated in excel "Accounting with $ and 2 decimal places. Then the data is loaded into mysql.

 

 

 

 

[Mchl]

How do they look when stored in MySQL?

[jakebur01]

Ohh, I see. When I looked at the mysql table. It was using the comma on the price $10,000.00 as a delimiter when processing the text file. Ha...

 

How can I get around this?

<?php
if($filetype==CSV)
{
$del=",";
}
else
{
$del="\t";
}




//Parse the columns by a specified deliminator
//$del = ","; //could be "   " or ";" or anything else you want
//$content = "Hi, Hello, Dog, Cat"; //for testing
$line = explode("\n", $content);

$cols = explode($del, $line[0]);

$sql = "CREATE TABLE `".$table_name."`(";
$i=1;
foreach($cols as $col){
   $sql .= "col".$i++." varchar(256) NULL default '0',";
   }
$sql  = rtrim($sql, ",");//Get rid of that last "," in the statment
$sql .= ");";
//Go ahead and execute however you want. Proble "mysql_query($sql) or die(mysql_error()."<br>".$sql);"
$_SESSION['table_name'] = $table_name; //Setting the table name into a session variable

$db = mysql_connect('connect info') or die(mysql_error()); 
mysql_select_db('database') or die(mysql_error()); 
mysql_query($sql) or die(mysql_error()."<br>".$sql);

$sambo="ALTER TABLE `".$table_name."` ENGINE=myisam";

mysql_query($sambo) or die(mysql_error()."<br>".$sambo);
  
echo "<br /><br />";
$source_file = "C:/Inetpub/Websites//mysite.com/$target";
$query = "load data infile \"$source_file\" INTO TABLE $table_name FIELDS TERMINATED BY ','";
// $query="LOAD DATA INFILE \"$target\" INTO TABLE $table_name FIELDS TERMINATED BY $del TERMINATED BY \n"; 
$result = mysql_db_query(database, $query, $db)
or die(mysql_error()); 
?>

 

 

[samshel]

i think it is better option not to write the prices with commas in the first place in the file which u r loading..ofcourse u should have control of that script..

 

if u want to do it here, u will have to loop through all rows using for loop, format the price properly and use INSERT...so u can no more use LOAD FILE

 

 

this is again as per my knowledge..there can be better ways.

[Mchl]

If I were you, I would create another column in this table (type DECIMAL). Then do a one time operation of moving all these values to proper format (probably possible with simple UPDATE query). Then drop the current column.

[jakebur01]

It was a matter of formating the columns in excel as NUMBER with two decimal places and no $ sign or comma.

 

Thank you for your help. I should have paid better attention to my flat file before uploading.

[Mchl]

Don't store numbers as strings.

For money DECIMAL is probably the best choice (second best is storing number of cents as integer)

[jakebur01]

Should I put something below to check and see if it is xx.xx and if it is, make a decimal column rather than varchar?

foreach($cols as $col){
   $sql .= "col".$i++." varchar(256) NULL default '0',";
   }

 

[Mchl]

Why are you trying to do it via PHP?

[jakebur01]

The flat file has a different number of columns every time. So I was steering away from making a static table to upload my flat files into.

[Mchl]

Uhm... Well that's kind of special case.

What does your application do?

[jakebur01]

It takes a price file from a vendor, makes a few calculations, and spits out a price file that will import into an inventory program.

[Mchl]

You sure you need MySQL for that?