How to use raw query in laravel and show ouput in blade page?

359
March 28, 2017, at 6:40 PM

In my controller i use raw query in this format

namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use App\MonthlyActivity;
class MonthlyActivityController extends Controller
{
    //
    public function show()
    {
        //$monthly = MonthlyActivity::all();
    //  $var =MonthlyActivity::connection()->getPdo()->quote($var);
        $query = "select  mon,year,
        combo,
       registered,
       forwardedbycmo,
       clarification,
       noaction,
       disposed,mon_srno,undertaken
from monthly_activities
union
select extract('month' from actiondate) as mon,extract('year' from actiondate) as year,
        extract('year' from actiondate)|| '-' ||to_char(to_timestamp (extract('month' from actiondate)::text, 'MM'), 'TMMon') as combo,
       sum(case when actioncode = '00' then 1 else 0 end) as registered,
       sum(case when actioncode = '4T' and fromorg='CMOFF' then 1 else 0 end) as forwardedbycmo,
       sum(case when actioncode = '4D' and fromorg='CMOFF' then 1 else 0 end) as clarification,
       sum(case when actioncode = '10' then 1 else 0 end) as noaction,
       sum(case when actioncode = '50' then 1 else 0 end) as disposed,null as mon_srno,
        sum(case when actioncode = '40' and fromorg='CMOFF' then 1 else 0 end) as undertaken
from actionhistory where extract(month from actiondate)=extract(month from current_date)
and extract(year from actiondate)=extract(year from current_date)
 group by mon,year order by year,mon;";
        $result = MonthlyActivity::select(MonthlyActivity::raw($query));
        //print_r($result);
        return view('show',['monthly' => $result]);
    }
}

And in show.blade.php

<?php
                foreach($monthly as $mon)
                {
                        ?>
                        <tr>
                            <td>
                            <?php echo $mon->combo ?>
                            </td>
                            <td>
                            <?php echo $mon->year ?>
                            </td><td>
                            <?php echo $mon->id ?>
                            </td>
                            <td>
                            <?php echo $mon->id ?>
                            </td><td>
                            <?php echo $mon->id ?>
                            </td><td>
                            <?php echo $mon->id ?>
                            </td><td>
                            <?php echo $mon->id ?>
                            </td>
                        </tr>
                        <?php
                }
                ?>

The problem I am facing is it doesn't show the data, and I don't know either query is running or not.

How can I execute this query and show query data in show.blade.php?

Answer 1

Try this:

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

Now pass this $data to blade view like:

return view('show', array('data' => $data));

and use it on view by applying foreach() on $data

READ ALSO
Symfony2 : error maximum function nesting level of &#39;100&#39; reached aborting symfony

Symfony2 : error maximum function nesting level of '100' reached aborting symfony

when I create the first employee, the tables that are linked to him by a relationship no longer appear, and I have these mistakes, I did not understand why he gives these errors,and how to fix it ??

313
How to get the specific key value of array in Php [duplicate]

How to get the specific key value of array in Php [duplicate]

This question already has an answer here:

280
Issue in loading image file in php

Issue in loading image file in php

I am facing some typical issue in loading image files in phpImage got successfully loaded but cannot get displayed properly

201
How to auto-resize image in CKEditor 4.6

How to auto-resize image in CKEditor 4.6

I am using CKEditor 46 (latest version)

453