Write a query manually into the laravel

Question

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 :

can you print $data and check what you get or you can dd($data) — Vision Coderz, Sep 22 '17 at 10:58

Vision Coderz · Answer 1 · 2017-09-22T11:14:04.537

4

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

edited Sep 22 '17 at 11:14

answered Sep 22 '17 at 11:06

Vision Coderz

8,257
5
41
57

You should reword some of your sentences. You've missed some words. :) – Ivanka Todorova Sep 22 '17 at 11:11
@IvankaTodorova.may be you said correct. as i mentioned ref .ieven i found better answer so i posted that question answer here.i never used db statement so i thought this will help.thanks – Vision Coderz Sep 22 '17 at 11:12
I want to export the query results into excel using maatwebsite, the result of trying I get the message, Object of class stdClass could not be converted to string, the following script exports excel. Excel::create('report', function($excel) use ($data){ $excel->sheet('sheet 1', function($sheet) use ($data) { $sheet->fromArray($data); ob_end_clean(); }); })->download('xlsx'); – Siti Rahmah Sep 22 '17 at 11:45
@SitiRahmah.better first you check $data haas array values so you check for next erorrs.debug will be easy – Vision Coderz Sep 22 '17 at 11:46

score 2 · Accepted Answer · edited Nov 26 '17 at 20:23

2

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.

edited Nov 26 '17 at 20:23

marc_s

732,580
175
1,330
1,459

answered Sep 22 '17 at 11:17

Zakaria Acharki

66,747
15
75
101

1

Thank you for this very helpful :) – Siti Rahmah Sep 22 '17 at 11:46

score 0 · Answer 3 · answered Sep 22 '17 at 11:00

0

You can write custom query like:

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

answered Sep 22 '17 at 11:00

Mayank Pandeyz

25,704
4
40
59

I did try and print a variable $data, I got a new error. ErrorException in HomeController.php line 130: Array to string conversion in HomeController.php line 130 at HandleExceptions->handleError('8', 'Array to string conversion', 'C:\xampp\htdocs\admin\local\app\Http\Controllers\HomeController.php', '130', array('data' => array(object(stdClass), object(stdClass)))) in HomeController.php line 130 – Siti Rahmah Sep 22 '17 at 11:06
That's because you are using `echo`, instead use `print_r( $data );` – Mayank Pandeyz Sep 22 '17 at 11:09
I want to export the query results into excel using maatwebsite, the result of trying I get the message, Object of class stdClass could not be converted to string, the following script exports excel. [code] Excel::create('report', function($excel) use ($data){ $excel->sheet('sheet 1', function($sheet) use ($data) { $sheet->fromArray($data); ob_end_clean(); }); })->download('xlsx'); [/code] – Siti Rahmah Sep 22 '17 at 11:39

SNG · Answer 4 · 2017-10-06T06:48:37.227

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;');

score 0 · Answer 5 · answered Sep 22 '17 at 11:10

0

You can do as follows :

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

answered Sep 22 '17 at 11:10

Pubudu Jayawardana

2,250
1
13
18

score 0 · Answer 6 · answered Sep 22 '17 at 11:21

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;'));

Write a query manually into the laravel

6 Answers6