Reputation: 7176
I have a table with dates and values, not all dates are available, so where they are not I need to show a zero:
$this->db->limit($days);
$dati = $this->db->query('SELECT sum(eur_80) as eur_80, day_stamp FROM table WHERE user_id = ? GROUP BY day_stamp ORDER BY rendi_id DESC', 119)->result();
echo "periodo,guadagno" . PHP_EOL;
$date = date('Y-m-d', strtotime('-' . $days . ' days'));
for ($d = 0; $d <= $days - 1; $d++) {
$day = date('Y-m-d', strtotime($date . " +" . $d . " day"));
foreach ($dati as $stats => $stat) {
if ($stat->day_stamp === $day) {
echo $stat->day_stamp . "," . decimali($stat->eur_80) . PHP_EOL;
} else {
echo date('Y-m-d', strtotime($date . " +" . $d . " day")) . ",0.0" . PHP_EOL;
}
}
}
This returns many rows:
periodo,guadagno
2017-05-09,0.0
2017-05-09,0.5385
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-09,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-10,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-11,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-12,0.0
2017-05-13,0.5680
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-13,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-14,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
2017-05-15,0.0
What I need is to show only one item per date, where am I doing wrong?
Upvotes: 1
Views: 66
Reputation: 4636
You had to remember if you found an item in your database and only if you find for a day anyone you had to make your output with zero.
$this->db->limit($days);
$dati = $this->db->query('SELECT sum(eur_80) as eur_80, day_stamp FROM table WHERE user_id = ? GROUP BY day_stamp ORDER BY rendi_id DESC', 119)->result();
echo "periodo,guadagno" . PHP_EOL;
$date = date('Y-m-d', strtotime('-' . $days . ' days'));
for ($d = 0; $d <= $days - 1; $d++) {
$day = date('Y-m-d', strtotime($date . " +" . $d . " day"));
$dayFound = false;
foreach ($dati as $stats => $stat) {
if ($stat->day_stamp === $day) {
echo $stat->day_stamp . "," . decimali($stat->eur_80) . PHP_EOL;
$dayFound = true;
}
}
if (!$dayFound)
echo date('Y-m-d', strtotime($date . " +" . $d . " day")) . ",0.0" . PHP_EOL;
}
Upvotes: 2