MySQL query doesn't work in Laravel but it works fine with mysqli_*

Question

I have a mysql query:

SELECT MAX(CAST(REPLACE(name,'test','') AS SIGNED)) FROM `accts` WHERE `name` LIKE 'test%';

If I run the query in the MySQL console it executes.

If I execute this query with mysqli_query in PHP

mysqli_query($connection, "SELECT max(cast(replace(name,'test','') as signed)) FROM accts WHERE name LIKE '$test%'")

it executes.

If I run it in laravel:

DB::connection('db')
                ->table('accts')
                ->select(DB::raw("max(cast(replace(name,'test','') as signed))"))
                ->where('name', 'like',  "'$test%'")
                ->get();

It throws an error:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(), MAX(), COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select max(cast(replace(name,'test','') as signed)) from `accts` where `name` like 'test%')

Does anybody have any idea what is happening? The error looks like it is coming from the server but the server has no problem with the query. Is there an issue with how laravel is executing the query?

It doesn't fix the error - but it should be `->where('name', 'like', "$test%")` — Paul Spiegel, Mar 06 '19 at 19:05
Does `DB::connection('db')->table('accts')->select(DB::raw("max(name)"))->get();` work? — Paul Spiegel, Mar 06 '19 at 19:27
It doesn't throw the group by error but it also doesn't do what the original query does. — ZCT, Mar 06 '19 at 19:42
Now change the simple query (from my comment) to yours step by step and see which change causes the error. This is basic debugging technique. — Paul Spiegel, Mar 06 '19 at 19:46

Bulfaitelo · Answer 1 · 2019-03-06T19:57:49.377

3

Your problem must be related to database configuration,

In your config/database.php turn off the strict mode ('strict' => false,).

Example:

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => null,
    ],

References:

Laravel Eloquent Where

Syntax error or access violation: 1140 Mixing of GROUP columns laravel

edited Mar 06 '19 at 19:57

answered Mar 06 '19 at 19:07

Bulfaitelo

515
7
18

This causes the exact same error. – ZCT Mar 06 '19 at 19:27
@ZCT new obs on my response – Bulfaitelo Mar 06 '19 at 19:41

MySQL query doesn't work in Laravel but it works fine with mysqli_*

1 Answers1