Looping through collection

replied 9 years ago

Solution

Have you googled grouping query with totals. If you can figure the regular sql for what you want, post it, then someone might be able to convert to eloquent. Or just use the regular sql. I myself use ms access to get a query, then I convert it to mysql. Of course you may not have ms access. But there or other tools available online to design a query. The raw mysql query is what you need first, then you can convert it to eloquent.

Last updated 9 years ago.

0

jimgwhit

replied 9 years ago

Not done in laravel, but here is a solution that can work, I think you will get the idea:
Controller code:

public function mytest() {
            $page='1';
        if (isset($_REQUEST['page'])) {
        $page = $_REQUEST['page'];
        }
        else {
        $page = '1';
        }
        $data['page'] = $page;
          $this->view->render('owner/mytest', $data);  
        }

View code, Yes accessing db in view, but almost have to in this case:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
    
<?php
$db = \helpers\database::get();
$rowsperpage = '5';
//$offset = 0;
$page = $data['page'];
echo 'page=='.$page;
$offset = ($page - 1) * $rowsperpage;
echo 'offset=='.$offset. "<br>";
$pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
$stmt = $db->select("SELECT * FROM ".PREFIX."powners ORDER BY oname " . $pagingQuery);   

foreach($stmt as $row){
                        $vowner = $row->ownerid;
			echo $row->oname . "<br>";
                        $stmt2 = $db->select("SELECT petname FROM ".PREFIX."pets WHERE ownerid = :vowner ORDER BY petname",
			 array('vowner' => $vowner));
                        foreach($stmt2 as $row2){
                            echo "-----". $row2->petname . "<br>";
                        }
		}

$pageno = $page + 1;
echo "<a href='".DIR."owner/mytest?page=".$pageno."'>next</a>";
?>

</body>
</html>

Here is sample output:
BLACKBURN
-----B8
-----JAVA
BOBBY
-----FLUFFYs
-----GIZMO's
-----suziedoggie
boy oh's
DANIEL
-----b10s
-----ztest3
DIANE
-----APRIL'S
-----BRAT
-----chocolate
-----GIANT
-----HOGAN
-----testoct25s
next

I'm listing owners with their pets, this is a test database I do all sorts of stuff on for testing.
Ignore names, just test data.

Last updated 9 years ago.

0

smithdp1

replied 9 years ago

jimgwhit thank you for making me think...justed needed an extra brain for a moment. Group by worked for this. Here is my solution.

// get date form submit
$date = Input::get('date');
// get item categories
$cats = DB::table('saleitems')
                 ->select('category_id', DB::raw('count(*) as total'))
                 ->groupBy('category_id')
                 ->where('created_at', 'LIKE', '%'.$date.'%')
                 ->get();
// get items
        $items = DB::table('saleitems')
                 ->select('product_id', DB::raw('count(*) as total'), 'category_id', 'prod_name')
                 ->groupBy('product_id')
                 ->where('created_at', 'LIKE', '%'.$date.'%')
                 ->get();

then i used foreach loops and if's

<table width="100%" class="table table-bordered table-striped">
<tbody>
  @foreach($cats as $cat)
  <tr><th width="90%">{{Category::where('id', '=', $cat->category_id)->first()->cat_name}}</th><th>QTY</th></tr>
   @foreach($items as $item)
   @if($item->category_id == $cat->category_id)
   <tr><td width="90%">{{$item->product_id}} - {{$item->prod_name}}</td><td> {{$item->total}}</td></tr>
   @endif
   @endforeach
   <tr><th width="90%">&nbsp;</th><th>Total - {{$cat->total}}</th></tr>
  @endforeach
</tbody> 
</table>

worked like a charm!

tahidur liked this reply

1

jimgwhit

replied 9 years ago

Yes, similar, you grouped, I just selected as I went, but glad you got it.

0

Moderators