How to update a total sum from a row based on id in php mysql

Question

I have the following columns in my database using MySQL.

 id|product_1|product_2|product_3|Total
 1    25         25        25       75

However, let's say that I want to update one specific row in a form i.e. product_2 by 1.

id|product_1|product_2|product_3|Total
1    25         26        25       76

How do I accomplish the above using either Raw or Eloquent MySQL?

I've tried so many things like changing my code, searching on Google, YouTube, but I couldn't find a solution.

So the code below is what loads my table with populated data from MySQL.

public function index()
{
    $ProductsPost = ProductsPost::all();

    $sum_total = ProductsPost::select(DB::raw('product_1 + product_2 + product_3 as total_products'))->where('id', 1)->total_products;
    return view('products/index')->with(
        [
            'products_post' => $ProductsPost,
            'sum_total' => $sum_total
        ]
    );
}

Below is my update function.

public function update(Request $request, $id)
{

    $ProductsPost = ProductsPost::find($id);

    $ProductsPost->update($request->all());

    ProductsPost::where('id', $id)->sum('product_1', 'product_2', 'product_3');

    if ($ProductsPost->wasChanged()) {
        // changes have been made
        return redirect('products')->with(['success' => 'Changes were successfully saved.']);
    } else {
        return redirect('products/'.$id.'/edit')->with(['error' => 'No changes made.']);
    }
}

Am I supposed to add an update here to the total column?

Again, the result I'm looking for is the total sum of columns in a row based on id. For example, if product_2 changes to a different value, I want that reflected for the Total column. The Total column should sum the collection of values from the three columns product_1, product_2, product_3.

Ideally, you wouldn't have a `Total` column, that'd be calculated manually as and when needed. However, in your scenario, yes, you'll need to update the total too. Related: https://stackoverflow.com/questions/12838729/multidimensional-array-array-sum — Script47, Jul 16 '19 at 08:49
Any time you find yourself with enumerated columns (above, say, 2), alarm bells should start ringing. Consider whether your schema design is optimal. — Strawberry, Jul 16 '19 at 09:41

score 0 · Accepted Answer · answered Jul 16 '19 at 09:57

Sounds like a generated column, you can use the storedAs($expression) modifier in your migration as following:

Schema::create('products_posts', function (Blueprint $table) {
  $table->double('product_1');
  $table->double('product_2');
  $table->double('product_3');
  $table->double('total')->storedAs('product_1 + product_2 + product_3');
});

or

You may use the virtualAS($expression) modifier, which its value will be calculated on the fly, but based on your scenario you want to store the value and be update on the insert or update.

See the Documentation and this Question too.

Note

product_1, product_2, product_3 as columns, looks like you need to do some normalization.

score 0 · Answer 2 · answered Jul 16 '19 at 19:27

0

You should add a 'On Update' constraint on your product_1, product_2 & product_3 column. On every update on this column, total will become sum of three product columns.

answered Jul 16 '19 at 19:27

Ajay Sharma

31
8

How to update a total sum from a row based on id in php mysql

2 Answers2

or

Note