0

I need to generate this kind of table from a database table. enter image description here

this is an output collection from my query

[{delivery_date: "2019-01-09", delivery_count: "5", price: "190.00", total_price: "950.00"}
{delivery_date: "2019-01-10", delivery_count: "45", price: "170.00", total_price: "7650.00"}
{delivery_date: "2019-01-10", delivery_count: "2", price: "50.00", total_price: "100.00"}
{delivery_date: "2019-01-10", delivery_count: "3", price: "60.00", total_price: "180.00"}
{delivery_date: "2019-01-10", delivery_count: "3", price: "185.00", total_price: "555.00"}
{delivery_date: "2019-01-16", delivery_count: "6", price: "50.00", total_price: "300.00"}]

and this is how I query the table

 $deliveries = DB::table('deliveries')
    ->where('user_id',Auth::id())
    ->groupBy('price','delivery_date')
    ->orderBy('delivery_date')
    ->select('delivery_date',DB::raw('SUM(number) as delivery_count'),'price', DB::raw('SUM(number*price) as total_price'))
    ->get();

and this is how I display the data

<table class="display table table-condensed table-bordered">
    <thead>
         <th>Date</th>
         @foreach($deliveries as $delivery)
             <th>{{$delivery->price}}</th>
         @endforeach
    </thead>
    <tbody>

         @foreach($deliveries as $delivery) 
              <tr>      
                  <td>{{ $delivery->delivery_date }}</td>   
                   @if($delivery->price = $deliveries[$i]->price)
                        <td>{{ $delivery->delivery_count }}</td>   
                   @else
                        <td>0</td>   
                   @endif                        
              </tr>
         @endforeach

     </tbody>
 </table>

this is what I get from my view

enter image description here

I am stuck here and still trying to figure out how to do it.. Please help. Thanks guys!

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Kim Carlo
  • 1,173
  • 3
  • 19
  • 47
  • Google for "MySQL pivot query" for a hint. – Tim Biegeleisen Jan 29 '19 at 05:56
  • I updated the images.. I am already getting the values i need but I need to merge some rows and columns – Kim Carlo Jan 30 '19 at 14:28
  • Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Will B. Jan 30 '19 at 17:06
  • 1
    You're looking specifically to use rows of data as columns, which is referred to as a Pivot Table. You can add a Total row using [`->groupBy(DB::raw('price, delivery_date WITH ROLLUP'))`](https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html). Alternatively you can use [PHP to organize your data](https://3v4l.org/ABqh6) - *fixed formatting of table* – Will B. Jan 30 '19 at 18:36

0 Answers0