Binding parameter to Db::raw laravel query

Question

I've got the following raw query:

$results = Db::select( Db::raw("SELECT HOUR(created_at) as hour, COUNT(*) as count
                       FROM `visited`
                       WHERE created_at >= DATE_SUB(NOW(),INTERVAL 16 DAY)
                       GROUP BY HOUR(created_at)") );

I need to parameterize the day interval, so I tried this:

$days = 16;
$results = Db::select( Db::raw("SELECT HOUR(created_at) as hour, COUNT(*) as count
                       FROM `visited`
                       WHERE created_at >= DATE_SUB(NOW(),INTERVAL :days DAY)
                       GROUP BY HOUR(created_at)", ["days" => $days]) );

But I am getting the following error:

"SQLSTATE[HY000]: General error: 2031

Apparently the binding is not working. What am I doing wrong?

miken32 · Accepted Answer · 2023-11-24 16:56:53Z

15

Answer recommended by PHP Collective

Try this:

$results = DB::select('SELECT HOUR(created_at) as hour, COUNT(*) as count FROM visited WHERE created_at >= DATE_SUB(NOW(),INTERVAL ? DAY) GROUP BY HOUR(created_at)', [16]);

You can even use named bindings:

$results = DB::select('SELECT HOUR(created_at) as hour, COUNT(*) as count FROM visited WHERE created_at >= DATE_SUB(NOW(),INTERVAL :days DAY) GROUP BY HOUR(created_at)', ['days' => 16]);

Don't need to use DB::raw(), just use DB::select() for simple raw select queries: https://laravel.com/docs/master/database#running-queries

edited Nov 24, 2023 at 16:56

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Jan 28, 2017 at 21:50

Artenes Nogueira

1,6021 gold badge16 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Binding parameter to Db::raw laravel query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related