手动将查询写入laravel

I have a little problem, I want to try to take the data with a custom query on laravel but when I try foreach I can't get the data. anyone can help me

This script on the controller :

  $data = DB::Statement('SELECT NM_PERUSAHAAN,
                             count(*) as total_count,
                             sum(FLAG_TERIMA) as approved,
                             sum(1 - FLAG_TERIMA) as not_approved
                             from MSTBEASISWAS
                             group by NM_PERUSAHAAN;');

foreach ($data as $datas) {
   echo $datas;
}

Error :

The DB::statement() method is used to execute SQL statements without returning result instead return true/false.

You're trying to use this boolean as a query result that why you've this message back from the foreach loop, if you want to run a select statement, you could use DB::select(), e.g :

DB::select('select query here');

Hope this helps.

You can write custom query like:

$data = DB::select(DB::raw('your query here'));

DB::statement will not return data. if you are performing queries which don't return data, then using a SELECT query will result errors. For example, if you want to start the auto-increment ID of a MySQL table to something other than zero, we can use the statement method.

for the above query you have to use DB::select.

$data=DB::Statement('SELECT NM_PERUSAHAAN,
                             count(*) as total_count,
                             sum(FLAG_TERIMA) as approved,
                             sum(1 - FLAG_TERIMA) as not_approved
                             from MSTBEASISWAS
                             group by NM_PERUSAHAAN;');

Here is the difference

DB::raw()

It generates a raw and sanitized SQL string, to be passed to other query/statements, preventing SQL injections. Is to be used with all of the and never alone. And you should never send a not sanitized string to your query/statements.

DB::select(DB::raw('select * from whatever'));

DB::select()

Is for simple selects:

DB::select(DB::raw('select * from whatever'));

DB::statement()

I think it work with selects, but should be used for non SQL query commands:

DB::statement(DB::raw('update whatever set valid = true;'));

DB::unprepared()

All SQL commands in Laravel are prepared by default, but sometimes you need to execute a command in an unprepared mode, because some commands in some database cannot be ran in prepared mode. Here's an issue I opened about this:

https://github.com/laravel/framework/issues/53

DB::unprepared(DB::raw('update whatever set valid = true;'));

Ref: Difference between Laravel's raw SQL functions

You can do as follows :

$data = DB::select($your_select_query);

The "statement" method of the DB facade returns a boolean value, which tells you whether the query execution was successful or not. Therefore foreach can not process it and throws an exception.

You can understand this by looking at the 2nd line of the exception stack trace.

array('data' => true)

So, to run a raw query string use the following code:

DB::select(DB::raw('SELECT NM_PERUSAHAAN,
                         count(*) as total_count,
                         sum(FLAG_TERIMA) as approved,
                         sum(1 - FLAG_TERIMA) as not_approved
                         from MSTBEASISWAS
                         group by NM_PERUSAHAAN;'));