Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

fahdshaykh's avatar

laravel query not working properly while working on sql database

i run this query on phpmyadmin database table working fine:

select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix from prefixes GROUP BY INET_ATON(subip)/256 order by cidr desc

when i run raw query in laravel:

$selected_prefixes = DB::select("select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix from prefixes GROUP BY INET_ATON(subip)/256 order by cidr desc ");

giving this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'portal.prefixes.prefix' isn't in GROUP BY (SQL: select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix from prefixes GROUP BY INET_ATON(subip)/256 order by cidr desc )

here example table file: https://www.mediafire.com/file/vwnply7ggtai52m/prefixes(5).sql/file

0 likes
5 replies
tykus's avatar

In your config/database.php; you can set options (such as sql_mode) for the connection:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
//            'strict' => true,
            'modes' => [
//                'ONLY_FULL_GROUP_BY',
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_AUTO_CREATE_USER',
                'NO_ENGINE_SUBSTITUTION',
            ],
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
Tray2's avatar

@fahadsheikh303 Strict is the way to go and solving your issue is pretty simple.

This is the issue

 'portal.prefixes.prefix' isn't in GROUP BY

And to solve that you just need to add it in the group by

So this should work.

$selected_prefixes = DB::select("select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix from prefixes GROUP BY INET_ATON(subip)/256 , prefix 
order by cidr desc ");
1 like

Please or to participate in this conversation.