Multiply 2 MySQL columns with decimals

Question

Multiply 2 MySQL columns with decimals

Navigation

#1 by (4 votes)

2

I have a column named PRECIO and another CANTIDAD , column PRECIO is in text format and the decimal separator has a comma, (this is the result of an excel export to mysql).

Achieve multiply PRECIO x CANTIDAD with this function:

tep_db_query("update " . TABLE_EXPORT . " set totales = cantidad * precio");

Now I need the result entered in totales to contain 2 decimals.

What remains to be done, to obtain the result with 2 decimals considering that the value to multiply that contains the decimal is comma and in text format?

NOTE: I can not pass the text format to decimal because when it imports the field omits the decimals.

while:

$file = $_FILES['file']['tmp_name']; $handle = fopen($file, "r"); $id = 0; while(($filesop = fgetcsv($handle, 1000, ";")) !== false) { $cantidad = $filesop[10]; $precio = $filesop[11]; $sql = mysql_query("INSERT INTO TABLAX (id, cantidad, precio) VALUES ('$id++','$cantidad','$precio') }

mysql php

asked by Ivan Diaz Perez 21.08.2017 в 15:36

source

1 answer

How to show number without commas in python C ++ error LNK2019 Unresolved external symbol

score 4 · Accepted Answer

You can use FORMAT in UPDATE in the following way to save the calculated data in a field of type TEXT , CHAR , etc:

UPDATE
  pruebas
SET
  totales = REPLACE(REPLACE(FORMAT(cantidad * precio, 2), ',', ''), '.', ',');

Here you can see a online example .

The number format in MySQL is, by default, #,###.## , so I first remove the commas from the thousands by replacing them with an empty string and then the decimal points by a comma, similarly I did in PHP with str_replace .

Ideally, you will use native MySQL data and when reading the excel file (which seems to be a CSV) do the conversion before saving the data:

$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$id = 0;
for ($id = 0; ($filesop = fgetcsv($handle, 1000, ";")) !== false; $id++) {
  $cantidad = mysql_real_escape_string(
    str_replace(',', '.', $filesop[10])
  );
  $precio = mysql_real_escape_string(
    str_replace(',', '.',$filesop[11])
  );
  $sql = mysql_query("
    INSERT INTO TABLAX (
      id,
      cantidad,
      precio
    ) VALUES (
      '$id',
      '$cantidad',
      '$precio'
    )
  ");
}

I strongly advise you to stop using mysql_* functions because they were marked obsolete in PHP 5.5 and completely removed from PHP 7.0, and migrate your code to PDO-mysql or mysqli .

Previous answer with examples of format in PHP and MySQL