I'm trying to get total number of item based on month from MSQL database using SLIM framework, When i display data i get result but separated even if there are in the same month. My code are,
$app->get('/view/test', function ($request, $response, array $args) {
try {
$con = $this->db;
date_default_timezone_set('UTC+3h');
$sql = "SELECT start_time,COUNT(*) as tt FROM parking_sessions GROUP
BY FROM_UNIXTIME(start_time/1000)";
$result = null;
$results = null;
foreach ($con->query($sql) as $row) {
$result['muda'] = date('M',($row['start_time']/1000));
$result['total'] = $row['tt'];
$results[] =$result;
$total = count($results);
}
if ($result) {
return $response->withJson(array('status' => 'true', 'date' =>
$results, 'jumla' => $total), 200);
} else {
return $response->withJson(array('status' => 'Owner Not Found'),
422);
}
} catch (\Exception $ex) {
return $response->withJson(array('error' => $ex->getMessage()), 422);
}
});
And it give me the following result
"date": [
{
"month": "Feb",
"total": "1"
},
{
"month": "Mar",
"total": "1"
},
{
"month": "Apr",
"total": "1"
},
{
"month": "Apr",
"total": "1"
}
],
And all result needed is like below
"date": [
{
"month": "Feb",
"total": "1"
},
{
"month": "Mar",
"total": "1"
},
{
"month": "Apr",
"total": "2"
}
],
And my DB look like
id slot_id customer_vehicle_id start_time end_time check_out_time status
1 1 2 1553249524000 1554346841515 1553111000 1
2 6 4 1554351619803 1553253124000 1553214094 0
3 6 4 1555224715000 1553253124000 1553214094 0
4 7 3 5086800000 1553253124000 1553214094 0
You cannot group by queries with unixtime as your getting every different second and minute in unix time. what you need is to convert it to date without time. SELECT start_time,COUNT(*) as tt FROM parking_sessions GROUP BY DATE (FROM_UNIXTIME(start_time/1000)) note: date will truncate minutes and you'll left with date only