CODEWITHSUNDEEP
phpdatetime
Mr.Web
Mr.Web

Reputation: 7176

PHP & MySQL, show empty days

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

Answers (1)

Sascha A.
Sascha A.

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

Related Questions