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: stackoverflow.com/questions/12838729/… — Script47
– Script47, Commented Jul 16, 2019 at 8: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
– Strawberry, Commented Jul 16, 2019 at 9:41

Omer Abdelmajeed · Accepted Answer · 2019-07-16 09:57:13Z

0

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.

answered Jul 16, 2019 at 9:57

Omer Abdelmajeed

3641 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajay Sharma · Accepted Answer · 2019-07-16 19:27:04Z

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, 2019 at 19:27

Ajay Sharma

318 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

or

Note

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

or

Note

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related