How to increment MySQL field by one if one or more items are checked in form?

Question

I have a page where user can vote which gaming platform (for example) is the best. In MySQL I have a table of these items. I want to update 'count' field and set it to +1 every time when specified item is checked. After form is submitted I get an array of selected items:

array [
  0 => "XBOX"
  1 => "PS4"
  2 => "PC"
]

Now, I want to check and compare this array with MySQL fields and increase their count values by 1. I am trying to compare array with MySQL with this, but unsuccessfully.

$value = Input::get('platform'); //this produces presented array
$test = Platform::where('name', 'LIKE', implode(" ", $value))->get();

Basically for 2 reasons 1) you dont seems to actually execute the query you build and 2) the query is invalid. You should be using a IN() — RiggsFolly, Aug 22 '16 at 19:17
I've changed it to IN and checked with `dd($test)`, but it shows an empty array. — harunB10, Aug 22 '16 at 19:20
With `Platform::whereIn('name', $value)->get();` I get what I want. Now I'm trying to update their count fields. `$test->count = $test->count + 1; $test->save();` is not working though. — harunB10, Aug 22 '16 at 19:29
@harunB10 check the update, if you are ok with using raw sql. Let me know if you get any error as I didn't test it. — jaysingkar, Aug 22 '16 at 19:54

jaysingkar · Accepted Answer · 2016-08-22T19:53:41.623

1

I think you should use whereIn instead of where

$test = Platform::whereIn('name', $value)->get();

Update:

You can do this by DB::raw():

DB::table('Platforms')
   ->whereIn('name', $value)
   ->update([
       'count' => DB::raw('count + 1')
   ]);

edited Aug 22 '16 at 19:53

answered Aug 22 '16 at 19:22

jaysingkar

4,315
1
18
26

It works like a charm. Is it ok to use DB::raw()? By "ok" I mean is it more safe and efficient than using Laravel Query Builder? Or there is no difference...? – harunB10 Aug 22 '16 at 22:37
1

@harunB10 DB::raw() is the method of Laravel Query Builder....So you can not compare between two same things... what you can compare is Eloquent and Query Builder..... Your approach was using Eloquent while the above is Query Builder... Query Builder are more efficient than Eloquent. Here's the link for comparision... https://blog.sriraman.in/laravel-eloquent-vs-fluent-query-builder/ – jaysingkar Aug 22 '16 at 22:47
I want to return to this question. I have a problem with incrementing the field value with this code. At the beginning it was working perfect. But as time goes by, it makes mistakes... In MySQL I have stored values that shows which user voted for which item... And when I do query: `SELECT COUNT(*) FROM 'users' WHERE vote = 'PC'`.. Here I get value 115, but in 'Platforms' table I have column count that shows 121 (votes). Can it be the case if users are voting at the same time? Is it problem with threads or something else? – harunB10 Sep 04 '16 at 21:29
@harunB10 does this difference remains all the time ? – jaysingkar Sep 05 '16 at 07:20
Difference is increasing as number of users are increasing too. I found something here - http://stackoverflow.com/questions/24017616/mysql-updating-same-row-by-multiple-users-like-counter on @Anthony Rutledge 's answer. Maybe it is because of the type. Now I changed it to InnoDB (previously it was MyISAM). For now it is working well. – harunB10 Sep 05 '16 at 08:14
Nope. Problem remains. Counter is not updated by +1 correctly. – harunB10 Sep 05 '16 at 18:09
ok.. can you try to execute the query using transactions ? that might solve the issue. – jaysingkar Sep 05 '16 at 18:17

score 1 · Answer 2 · answered Aug 22 '16 at 19:24

1

I'm not familiar with your query builder, but in SQL you would just do something like this:

UPDATE table_name SET counter=(counter+1) WHERE name IN ('XBOX', 'PS4', PC');

Where table_name is your table name and counter is the name of the field that keeps track of the counts.

This will increment each matched platform by 1.

answered Aug 22 '16 at 19:24

Brad

96
7

nice work.. updated my answer to do the same thing in Laravel – jaysingkar Aug 22 '16 at 22:49

score 0 · Answer 3 · answered Aug 22 '16 at 19:50

Now it works. If someone needs code example, here it is. Thank you guys.

public function vote()
{

    $value= Input::get('platform');

    $test = Platform::whereIn('name', $value)->get();

    for ($i = 0; $i < count($test); $i++) {
        $test[$i]->count += 1;
        $test[$i]->save();
    }
}

How to increment MySQL field by one if one or more items are checked in form?

3 Answers3

Update: