Sum of 3 numeric values stored in a single mysql field

PHP
1

Hi everyone! I know that can’t be correct but I would have to sum 3 numeric values stored in a single mysql field.
In the field called valori_varianti I have 3 decimal numbers 1.00 1.50 1.50. How can I get the sum of these numbers?
I tried to use str_replace to get a result similar to this 1.00+1.50+1.50 but anyway if I recall the sum I don’t get the result of 4.00 but the string 1.00+1.50+1.50.
I also tried using array_sum but it still doesn’t work.
I don’t know if I explained myself well and I apologize if my English is not correct but I use google translate.
aggiunte

1 Like
2

You are 100% correct that its not correct to store 3 values in a single field. Bad database design.

explode the value into 3 values in an array, and then array_sum it.

3

Thanks a lot, I’m the first to reply. I did it! In fact I wanted to understand if it could be done.
Best regards

1 Like
4

The SUM of 3 numeric values stored in a single MySQL field can be obtained using the SQL statement:

SELECT SUM(field_name) FROM table_name;

5

But it is also bad style to store redundant data in the database. As you can sum the three values anytime you need it with SUM() you should not store the result anywhere.

6

That doesn’t seem to work for the OPs badly-designed database. It only appears to sum the first number in the string.
image

When I saw the question I did go down a rabbit-hole of manipulating sub-strings to have the summation done by the database engine, but I couldn’t get it to work.

1 Like
7

I mean, If you stipulate it would have exactly 3 values separated by spaces, it could be done with a combination of substrings, casts, charindex, etc… but it’s more effort than its worth.

8

However everything works fine with explode and array_sum the result is perfect. Thank you all!

9

Instead of continuing down this road, why not create a new table called valori_variantis with fields id (auto increment), valori_id (or proper name identifying the id of ther table you currently have, and valori_varianti. Write a script to call these records, explode, trim values and insert them into the new table as single values.

10

I’ve already created the variant table that references the products. The values_variants field that I indicated above is the one present in the orders table. For each product add one or more variants present in the variants table with product id. After placing the order, a query enters the variants chosen by the user associated with the order in the orders table.

11

It is good that you have variant table holding all possible variants for a product to choose from.

What many are trying to get across is that anytime a characteristic or field for any 1 item has more than 1 possible value, it should not be stored in the same table field.

For a simple example say you have a table called cars where you hold standard characteristics of the car in different fields, but this car comes in 8 colors. These colors would be stored in another table called car_colors and would have the car_id and color and there would be 8 records, one for each color with the car_id.

The same should apply with your orders. You can name the tables what you want but the point is this,

  • You have 1 order record.
  • Any number of products might be in an products_ordered table each having an order_id from that first order table.
  • Each product has a number pricing variants based on the options chosen, this is stored in its own table (call it what you want) with id,order_id, product_id, variant_id, and probably the current $ amount from the variant table as prices might change over time.

SO as you can see anytime there is more than 1 it is stored in a different table but references the related preceding tables.e.g.
1 order,
2 products each with
3 variants stored in these 3 tables.
And sure there are store related tables like products and variants but we are focused on the variants chosen for each product on one order.

1 Like