I'm trying to display data that shows the count for each month for locations in Jan-December.
I wrote the query to get the necessary data from the server but when I display it in the view in my table the layout for the data, doesn't look the way I want it to look like in my table. I believe I have to reconstruct the array that I wrote in the query for the right format first and then I can display it in my table?
Right now, the data that comes back from the server looks like so
<table id="registeredTable" class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>Locations</th>
<th>January</th>
<th>Feburary</th>
<th>March</th>
<th>April</th>
<th>May</th>
<th>June</th>
<th>July</th>
<th>August</th>
<th>September</th>
<th>October</th>
<th>November</th>
<th>December</th>
</tr>
</thead>
<tbody>
<?php
foreach($selectAllmonthlylocations as $item) {
echo '
<tr>
<td>'.$item["locations"].'</td>
<td>'.$item["Total"].'</td>
</tr>
';
}
?>
</tbody>
</table>
So my question is how can I format my array properly to display it in my table how I want?
Array
(
[0] => Array
(
[usergroup] => Austin_Domestic
[DateAdded] => 2018-01-03
[Total] => 15
)
[1] => Array
(
[usergroup] => Austin_International
[DateAdded] => 2018-01-25
[Total] => 2
)
[2] => Array
(
[usergroup] => BayArea_Domestic
[DateAdded] => 2018-01-16
[Total] => 192
)
[3] => Array
(
[usergroup] => BayArea_International
[DateAdded] => 2018-01-05
[Total] => 28
)
[4] => Array
(
[usergroup] => Bengaluru_Domestic
[DateAdded] => 2018-01-10
[Total] => 2
)
[5] => Array
(
[usergroup] => Bengaluru_International
[DateAdded] => 2018-01-05
[Total] => 1
)
[6] => Array
(
[usergroup] => Cork
[DateAdded] => 2018-01-02
[Total] => 31
)
[7] => Array
(
[usergroup] => CulverCity
[DateAdded] => 2018-01-10
[Total] => 3
)
[8] => Array
(
[usergroup] => Denver
[DateAdded] => 2018-01-10
[Total] => 1
)
[9] => Array
(
[usergroup] => Hyderabad
[DateAdded] => 2018-01-05
[Total] => 3
)
[10] => Array
(
[usergroup] => London
[DateAdded] => 2018-01-02
[Total] => 10
)
[11] => Array
(
[usergroup] => Macau
[DateAdded] => 2018-01-17
[Total] => 1
)
[12] => Array
(
[usergroup] => Munich
[DateAdded] => 2018-01-02
[Total] => 6
)
[13] => Array
(
[usergroup] => Sacramento_Domestic
[DateAdded] => 2018-01-04
[Total] => 1
)
[14] => Array
(
[usergroup] => Shanghai
[DateAdded] => 2018-01-12
[Total] => 2
)
[15] => Array
(
[usergroup] => Singapore
[DateAdded] => 2018-01-03
[Total] => 8
)
[16] => Array
(
[usergroup] => Sydney
[DateAdded] => 2018-01-21
[Total] => 1
)
[17] => Array
(
[usergroup] => Tokyo
[DateAdded] => 2018-01-04
[Total] => 3
)
[18] => Array
(
[usergroup] => Austin_Domestic
[DateAdded] => 2018-02-01
[Total] => 31
)
[19] => Array
(
[usergroup] => Austin_International
[DateAdded] => 2018-02-19
[Total] => 2
)
[20] => Array
(
[usergroup] => Bangkok
[DateAdded] => 2018-02-07
[Total] => 1
)
[21] => Array
(
[usergroup] => BayArea_Domestic
[DateAdded] => 2018-02-08
[Total] => 165
)
[22] => Array
(
[usergroup] => BayArea_International
[DateAdded] => 2018-02-12
[Total] => 29
)
Here is my code:
$selectallmonthlysql = 'SELECT locations, DateAdded, COUNT(*) as Total
FROM testserverdataSignup
WHERE DateAdded > "2017-12-31"
GROUP BY month(DateAdded), locations';
$selectAllmonthlystmt = $dbo->prepare($selectallmonthlysql);
$selectAllmonthlystmt->execute();
$selectAllmonthlylocations = $selectAllmonthlystmt->fetchAll(PDO::FETCH_ASSOC);
Screenshot of what the table currently looks like: I want the individual count for each location to correlate to the month and go across instead of down
</div>