Mysql: General error incorrect integer value

Question

I know this question is already asked, but I can't find a good answer.

View looks like:

@foreach ($projects as $key => $project)
                    <tr id="{{$project->id}}">
                      <td>{{$project->id}}</td>
                      <td>{{$project->slug}}</td>
                      <td>{{$project->order}}</td>
                      <td>{{$project->public}}</td>
                      <td><a href="{{ route('admin.projects.show', $project->id)}}" class="btn btn-info btn-sm">View</a> <a href="{{ route('admin.project.edit', $project->id)}}" class="btn btn-success btn-sm">Edit</a></td>
                    </tr>
@endforeach

Ajax code looks like:

$("tbody").sortable({
    items: "> tr",
    appendTo: "parent",
    helper: "clone",
    update: function( event, ui ) {
        let newOrder = $(this).sortable('toArray');
        $.ajax({
            type: "POST",
            url:'/admin/projects/updateOrder',
            data: {ids: newOrder}
        })
       .done(function( msg ) {
            // render table with new order?
       });
    }
}).disableSelection();

Controller function looks like:

public function updateOrder(Request $request)
    {
        $ids = $request->ids;
        // en el array ids, los "keys" serían el orden, los cuales puedes modificar según lo que requieras

        $caseQuery = 'CASE id ';
        foreach ($ids as $order => $id) {
            $caseQuery .= "WHEN $id THEN $order ";
        }
        $caseQuery .= ' END CASE';
            DB::table('projects')
                ->whereIn('id', $ids)
                ->update(['order' => $caseQuery]);
    }    

Error:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'CASE 
id WHEN 2 THEN 0 WHEN 3 THEN 1 WHEN 1 THEN 2 WHEN 4 THEN 3 WHEN 5 
THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 WHEN 8 THEN 7 WHEN 9 T' for column 
'order' at row 1 (SQL: update `projects` set `order` = CASE id WHEN 2 
THEN 0 WHEN 3 THEN 1 WHEN 1 THEN 2 WHEN 4 THEN 3 WHEN 5 THEN 4 WHEN 6 
THEN 5 WHEN 7 THEN 6 WHEN 8 THEN 7 WHEN 9 THEN 8 WHEN 10 THEN 9 WHEN 
11 THEN 10 WHEN 12 THEN 11 WHEN 13 THEN 12 WHEN 14 THEN 13 WHEN 15 
THEN 14 WHEN 16 THEN 15 WHEN 17 THEN 16 WHEN 18 THEN 17 WHEN 19 THEN 
18 WHEN 20 THEN 19 WHEN 21 THEN 20 WHEN 22 THEN 21 WHEN 23 THEN 22 
WHEN 24 THEN 23 WHEN 25 THEN 24 WHEN 26 THEN 25 WHEN 27 THEN 26 WHEN 
28 THEN 27 WHEN 29 THEN 28 END CASE where `id` in (2, 3, 1, 4, 5, 6, 
7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 
25, 26, 27, 28, 29))

¿Anyone know why I get this error?

This code is to update values when I do drag and drop.

Thanks.

UPDATE

Controller function now looks like here:

public function updateOrder(Request $request)
    {
        $ids = $request->ids;
        // en el array ids, los "keys" serían el orden, los cuales puedes modificar según lo que requieras

        $caseQuery = 'CASE';
        foreach ($ids as $order => $id) {
            $caseQuery .= "WHEN id = $id THEN $order ";
        }
        $caseQuery .= 'ELSE null';
        $caseQuery .= ' END';
            DB::table('projects')
                ->whereIn('id', $ids)
                ->update(['order' => $caseQuery]);

    }

The error I found on laravel.log is this:

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'CASEWHEN id = 26 THEN 0 WHEN id = 23 THEN 1 WHEN id = 19 THEN 2 WHEN id = 25 THEN 3 WHEN id = 27 THEN 4 WHEN id = 18 THEN 5 WHEN' for column 'order' at row 1 (SQL: update projects set order = CASEWHEN id = 26 THEN 0 WHEN id = 23 THEN 1 WHEN id = 19 THEN 2 WHEN id = 25 THEN 3 WHEN id = 27 THEN 4 WHEN id = 18 THEN 5 WHEN id = 28 THEN 6 WHEN id = 14 THEN 7 WHEN id = 24 THEN 8 WHEN id = 20 THEN 9 WHEN id = 12 THEN 10 WHEN id = 13 THEN 11 WHEN id = 17 THEN 12 WHEN id = 29 THEN 13 WHEN id = 15 THEN 14 WHEN id = 21 THEN 15 WHEN id = 16 THEN 16 WHEN id = 22 THEN 17 WHEN id = 4 THEN 18 WHEN id = 3 THEN 19 WHEN id = 1 THEN 20 WHEN id = 6 THEN 21 WHEN id = 9 THEN 22 WHEN id = 8 THEN 23 WHEN id = 5 THEN 24 WHEN id = 2 THEN 25 WHEN id = 10 THEN 26 WHEN id = 11 THEN 27 WHEN id = 7 THEN 28 ELSE null END where id in (26, 23, 19, 25, 27, 18, 28, 14, 24, 20, 12, 13, 17, 29, 15, 21, 16, 22, 4, 3, 1, 6, 9, 8, 5, 2, 10, 11, 7)) in /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Database/Connection.php:647 Stack trace: #0 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Database/Connection.php(607): Illuminate\Database\Connection->runQueryCallback('update project...', Array, Object(Closure)) #1 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Database/Connection.php(477): Illuminate\Database\Connection->run('updateproject...', Array, Object(Closure)) #2 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Database/Connection.php(416): Illuminate\Database\Connection->affectingStatement('update project...', Array) #3 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2152): Illuminate\Database\Connection->update('updateproject...', Array) #4 /home/vagrant/Code/Manifiesto/app/Http/Controllers/AdminController.php(141): Illuminate\Database\Query\Builder->update(Array) #5 [internal function]: App\Http\Controllers\AdminController->updateOrder(Object(Illuminate\Http\Request)) #6 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(55): call_user_func_array(Array, Array) #7 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(44): Illuminate\Routing\Controller->callAction('updateOrder', Array) #8 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Route.php(203): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(App\Http\Controllers\AdminController), 'updateOrder') #9 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Route.php(160): Illuminate\Routing\Route->runController() #10 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Router.php(574): Illuminate\Routing\Route->run() #11 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Routing\Router->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #12 /home/vagrant/Code/Manifiesto/vendor/vsch/laravel-translation-manager/src/RouteAfterMiddleware.php(21): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #13 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Vsch\TranslationManager\RouteAfterMiddleware->handle(Object(Illuminate\Http\Request), Object(Closure)) #14 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #15 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(41): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #16 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Routing\Middleware\SubstituteBindings->handle(Object(Illuminate\Http\Request), Object(Closure)) #17 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #18 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(43): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #19 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Auth\Middleware\Authenticate->handle(Object(Illuminate\Http\Request), Object(Closure), 'admin') #20 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #21 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(65): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #22 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle(Object(Illuminate\Http\Request), Object(Closure)) #23 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #24 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #25 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\View\Middleware\ShareErrorsFromSession->handle(Object(Illuminate\Http\Request), Object(Closure)) #26 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #27 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(64): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #28 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure)) #29 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #30 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #31 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(Object(Illuminate\Http\Request), Object(Closure)) #32 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #33 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(59): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #34 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure)) #35 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #36 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(102): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #37 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Router.php(576): Illuminate\Pipeline\Pipeline->then(Object(Closure)) #38 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Router.php(535): Illuminate\Routing\Router->runRouteWithinStack(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request)) #39 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Router.php(513): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request)) #40 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request)) #41 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http{closure}(Object(Illuminate\Http\Request)) #42 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(30): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #43 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure)) #44 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #45 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(30): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #46 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure)) #47 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #48 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #49 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle(Object(Illuminate\Http\Request), Object(Closure)) #50 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #51 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(46): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #52 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(148): Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle(Object(Illuminate\Http\Request), Object(Closure)) #53 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request)) #54 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(102): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request)) #55 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(151): Illuminate\Pipeline\Pipeline->then(Object(Closure)) #56 /home/vagrant/Code/Manifiesto/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request)) #57 /home/vagrant/Code/Manifiesto/public/index.php(53): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request)) #58 {main}

Answer 1

You are building your SQL incorrectly because of whitespace.

This code

$caseQuery = 'CASE';
foreach ($ids as $order => $id) {
    $caseQuery .= "WHEN id = $id THEN $order ";
}

Is resulting in an SQL statement

CASEWHEN id = 26 ...

Change it to

$caseQuery = 'CASE ';
foreach ($ids as $order => $id) {
    $caseQuery .= " WHEN id = $id THEN $order ";
}

and you get

CASE WHEN id = 26 ...

NOTE: You will have to do this for your entire query.

Answer 2

Try this and then check your log inside your app.

I added id on the WHEN claus(But if its some other variable change it to the appropriate)

UPDATE `projects` SET `order` = 
     CASE
            WHEN id = 2 THEN 0
            WHEN id = 3 THEN 1
            WHEN id = 1 THEN 2
            WHEN id = 4 THEN 3
            WHEN id = 5 THEN 4
            WHEN id = 6 THEN 5
            WHEN id = 7 THEN 6
            WHEN id = 8 THEN 7
            WHEN id = 9 THEN 8
            WHEN id = 10 THEN 9
            WHEN id = 11 THEN 10
            WHEN id = 12 THEN 11
            WHEN id = 13 THEN 12
            WHEN id = 14 THEN 13
            WHEN id = 15 THEN 14
            WHEN id = 16 THEN 15
            WHEN id = 17 THEN 16
            WHEN id = 18 THEN 17
            WHEN id = 19 THEN 18
            WHEN id = 20 THEN 19
            WHEN id = 21 THEN 20
            WHEN id = 22 THEN 21
            WHEN id = 23 THEN 22
            WHEN id = 24 THEN 23
            WHEN id = 25 THEN 24
            WHEN id = 26 THEN 25
            WHEN id = 27 THEN 26
            WHEN id = 28 THEN 27
            WHEN id = 29 THEN 28
        ELSE ?
        END
    WHERE id in (2, 3, 1, 4, 5, 6,7, 8,
                 9, 10, 11, 12, 13, 14, 
                 15, 16, 17, 18, 19,20, 
                 21, 22, 23, 24, 25, 26, 
                 27, 28, 29)

SO change this

$caseQuery = 'CASE id ';
        foreach ($ids as $order => $id) {
            $caseQuery .= "WHEN $id THEN $order ";
        }
        $caseQuery .= ' END CASE';
            DB::table('projects')
                ->whereIn('id', $ids)
                ->update(['order' => $caseQuery]);

TO this:

$caseQuery = 'CASE';
        foreach ($ids as $order => $id) {
            $caseQuery .= "WHEN id = $id THEN $order ";
        }
        $caseQuery .= 'ELSE null';
        $caseQuery .= ' END';
            DB::table('projects')
                ->whereIn('id', $ids)
                ->update(['order' => $caseQuery]);