Echo data from SQL table column into HTML row

Question

I have this Laravel code to retrieve data from database:

$stats = DB::table('jobRequirements')
    ->join('jobs', 'jobRequirements.job', '=', 'jobs.id')
    ->join('types', 'jobRequirements.type', '=', 'types.id')
    ->join('users', 'jobs.user', '=', 'users.id')
    ->select(DB::raw('users.name as user, SUM(jobRequirements.number) as number, types.name as type'))
    ->where('jobRequirements.active', 1)
    ->groupBy('types.id')
    ->orderBy('users.name'asc')
    ->get();

return view('stats',compact('stats'));

Then I display it in table with Laravel blade like this:

@foreach($stats as $s)
    <tr>
    <td>{{$s->user}}</td>   
    <td>{{$s->number}}</td>
    <td>{{$s->type}}</td>
    </tr>
@endforeach

With that I get this:

table {
  border-collapse: collapse;
}
table,
td {
  border: 1px solid black;
}
td {
  padding: 5px;
}



<table>
  <tr>
    <td>User1</td>
    <td>3</td>
    <td>Type2</td>
  </tr>
  <tr>
    <td>User1</td>
    <td>1</td>
    <td>Type3</td>
  </tr>
  <tr>
    <td>User2</td>
    <td>2</td>
    <td>Type1</td>
  </tr>
  <tr>
    <td>User4</td>
    <td>7</td>
    <td>Type3</td>
  </tr>
  <tr>
    <td>User4</td>
    <td>2</td>
    <td>Type6</td>
  </tr>
</table>

But I need it like this:

table {
  border-collapse: collapse;
}
table,
td {
  border: 1px solid black;
}
td {
  padding: 5px;
}



<table>
  <tr>
    <td>User name</td>
    <td>Type1</td>
    <td>Type2</td>
    <td>Type3</td>
    <td>Type6</td>
  </tr>
  <tr>
    <td>User1</td>
    <td>0</td>
    <td>3</td>
    <td>1</td>
    <td>0</td>
  </tr>
  <tr>
    <td>User2</td>
    <td>2</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>User4</td>
    <td>0</td>
    <td>0</td>
    <td>7</td>
    <td>2</td>
  </tr>
</table>

How can I achieve that?

I don't need complete solution, some examples or directions would be nice. It can be in plain PHP also.

Answer 1

You have two solutions:

1. Fix your query so you can get something like this (I assume your types are fixed?!) SELECT username, number of type1, number of type2, number of type3, number of type 6

2. Before return view(..) in your controller store your data in this format:

[username] = ['type1' = a number, 'type2' => a number', 'type3' => a number', 'type7' => a number']

$data = array();
// foreach row
foreach ($stats as $key => $value) {
    // if user doesn't exist in the array
    if (!array_key_exists($value->user, $data))  {
        // Add the user to the array
        $data[$value->user] = array('type1' => 0, 'type2' => 0, 'type3' => 0, 'type6' => 0);
    }
    // Add the number of the type to the corresponded type
    // Optional: You can do a check before adding if the type is already added
    $data[$value->user][$value->type] = $value->number;
}

And pass $data to your view and in the view you do:

@foreach($stats as $key => $value)
<tr>
<td>{{$key}}</td>
<td>{{$value['type1']}}</td>
<td>{{$value['type2']}}</td>
<td>{{$value['type3']}}</td>
<td>{{$value['type7']}}</td>
</tr>
@endforeach

I hope I could help you a bit.

Answer 2

Try this,

foreach($stats as $key => $s){
   $user .= '<td>'.$s->user.'</td>';
   $number .= '<td>'.$s->number.'</td>';
   $type .= '<td>'.$s->type.'</td>';
 }

To print table,

<table>     
<tbody>
    <tr>
        <td><b>User</b></td>{{ $user }}
    </tr>
    <tr>
        <td><b>Number</b></td>{{ $number }}
    </tr>
    <tr>
        <td><b>Type</b></td>{{ $type }}
    </tr>
</tbody>