Fill missing numbers from a sequence

Question

Hello,
I’m putting together a statistical system.
The result is an Array with "time" and "views".
I need to increment this data with 24 hours a day so that the graph is complete.

    $sql = "SELECT HOUR(data) as hora, COUNT(id) as views "
            . "FROM trafego "
            . "WHERE data >= '{$periodo}' "
            . "GROUP BY hora ";

    $query = $this->db->query($sql);
    $result = $query->fetchALL(PDO::FETCH_OBJ);

THE RESULT I HAVE IS

     0 =>
          object(stdClass)[4]
          public 'hora' => string '1' (length=1)
          public 'views' => string '3' (length=1)
     1 =>
          object(stdClass)[5]
          public 'hora' => string '4' (length=1)
          public 'views' => string '3' (length=1)
     2 =>
          object(stdClass)[6]
          public 'hora' => string '8' (length=1)
          public 'views' => string '5' (length=1)
     3 => 
          object(stdClass)[7]
          public 'hora' => string '10' (length=2)
          public 'views' => string '4' (length=1)
     4 =>
          object(stdClass)[8]
          public 'hora' => string '11' (length=2)
          public 'views' => string '7' (length=1)
     5 =>
          object(stdClass)[9]
          public 'hora' => string '21' (length=2)
          public 'views' => string '3' (length=1)
     6 =>
          object(stdClass)[10]
          public 'hora' => string '22' (length=2)
          public 'views' => string '3' (length=1)

How to increment this array with the hours and add 0 views?

Example, I have the schedules 1, 4, 8, 10 ,11, 21, 22

I need to add missing times and these must have 0 views

hora    views
1    -> 3
2    -> 0 (faltante, por isso deve ser 0)
3    -> 0 (faltante, por isso deve ser 0)
4    -> 3

So follows up to 24 hours. (0 ~ 23)

Answer 1

The solution is to first create an array with all desired times and zero values, then just update the array with the values you have. Below the code that shows it:

// O resultado obtido do banco de dados
$result = [
    (object)['hora' => 1, 'views' => 3],
    (object)['hora' => 4, 'views' => 3],
    (object)['hora' => 8, 'views' => 5],
    (object)['hora' => 10, 'views' => 4],
    (object)['hora' => 11, 'views' => 7],
    (object)['hora' => 21, 'views' => 3],
    (object)['hora' => 22, 'views' => 3]
];

/*
 * Inicia a estatística com todos os horários possíveis e com valores zerados.
 *
 * O segredo é utilizar a hora como chave do array,
 * isso facilita o preenchimento dos horários conhecidos na próxima etapa.
 */
for ($hora = 0; $hora < 24; $hora++) {
    $estatistica[$hora] = (object)['hora' => $hora, 'views' => 0];
}

/*
 * Atualiza a estatística com os horários que possui views.
 *
 * Aqui você deve percorrer o resultado do banco,
 * também deve ser utilizada a hora como chave para acessar
 * o item correspondente no array que atualmente está zerado
 * e atualizar a quantidade de views pelo valor real.
 */
foreach ($result as $horario) {
    $estatistica[$horario->hora] = $horario;
}

// Mostra o resultado
echo '<pre>';
print_r($estatistica);
echo '</pre>';

The result will be:

Array
(
    [0] => stdClass Object
        (
            [hora] => 0
            [views] => 0
        )

    [1] => stdClass Object
        (
            [hora] => 1
            [views] => 3
        )

    [2] => stdClass Object
        (
            [hora] => 2
            [views] => 0
        )

    [3] => stdClass Object
        (
            [hora] => 3
            [views] => 0
        )

    [4] => stdClass Object
        (
            [hora] => 4
            [views] => 3
        )

    [5] => stdClass Object
        (
            [hora] => 5
            [views] => 0
        )

    [6] => stdClass Object
        (
            [hora] => 6
            [views] => 0
        )

    [7] => stdClass Object
        (
            [hora] => 7
            [views] => 0
        )

    [8] => stdClass Object
        (
            [hora] => 8
            [views] => 5
        )

    [9] => stdClass Object
        (
            [hora] => 9
            [views] => 0
        )

    [10] => stdClass Object
        (
            [hora] => 10
            [views] => 4
        )

    [11] => stdClass Object
        (
            [hora] => 11
            [views] => 7
        )

    [12] => stdClass Object
        (
            [hora] => 12
            [views] => 0
        )

    [13] => stdClass Object
        (
            [hora] => 13
            [views] => 0
        )

    [14] => stdClass Object
        (
            [hora] => 14
            [views] => 0
        )

    [15] => stdClass Object
        (
            [hora] => 15
            [views] => 0
        )

    [16] => stdClass Object
        (
            [hora] => 16
            [views] => 0
        )

    [17] => stdClass Object
        (
            [hora] => 17
            [views] => 0
        )

    [18] => stdClass Object
        (
            [hora] => 18
            [views] => 0
        )

    [19] => stdClass Object
        (
            [hora] => 19
            [views] => 0
        )

    [20] => stdClass Object
        (
            [hora] => 20
            [views] => 0
        )

    [21] => stdClass Object
        (
            [hora] => 21
            [views] => 3
        )

    [22] => stdClass Object
        (
            [hora] => 22
            [views] => 3
        )

    [23] => stdClass Object
        (
            [hora] => 23
            [views] => 0
        )

)

Answer 2

/*
Array de teste. Existe apenas 1, 4, 8 e 11
*/
$result = array(
    0 => (object)array(
        'hora' => 1,
        'views' => 3
    ),
    1 => (object)array(
        'hora' => 4,
        'views' => 3
    ),
    2 => (object)array(
        'hora' => 8,
        'views' => 3
    ),
    3 => (object)array(
        'hora' => 11,
        'views' => 3
    )
);

$data_new = array();
for ($i = 0; $i < 24; $i++) {
    foreach ($result as $k => $v) {
        if ($v->hora == $i) {
            /*
            Atribui o valor existente a um novo array
            Isso também garante que a ordem dos horários seja igual as chaves array.
            */
            $data_new[$i] = $result[$k];

            /*
            Apaga essa chave para evitar percorrer novamente esse valor em todas as iterações.
            Como o horário é único, não tem com o que se preocupar.
            */
            unset($result[$k]);

            /*
            O amado e odiado "goto".
            Se a linguagem disponibiliza esse recurso, presente em muitas linguagens, por que não usá-lo?
            O uso aqui simplifica uso de break e uma flag para pular também a iteração principal.
            */
            goto next;
        }
    }

    /*
    Atribui 0 views para um horário inexistente.
    */
    if (!isset($data_new[$i])) {
        $data_new[$i] = (object)array(
            'hora'=>$i,
            'views'=>0
        );
    }
    next:
}

/*
Mostra o resultado
*/
print_r($data_new);

It results in

Array
(
    [0] => stdClass Object
        (
            [hour] => 0
            [views] => 0
        )

    [1] => stdClass Object
        (
            [hour] => 1
            [views] => 3
        )

    [2] => stdClass Object
        (
            [hour] => 2
            [views] => 0
        )

    [3] => stdClass Object
        (
            [hour] => 3
            [views] => 0
        )

    [4] => stdClass Object
        (
            [hour] => 4
            [views] => 3
        )

    [5] => stdClass Object
        (
            [hour] => 5
            [views] => 0
        )

    [6] => stdClass Object
        (
            [hour] => 6
            [views] => 0
        )

    [7] => stdClass Object
        (
            [hour] => 7
            [views] => 0
        )

    [8] => stdClass Object
        (
            [hour] => 8
            [views] => 3
        )

    [9] => stdClass Object
        (
            [hour] => 9
            [views] => 0
        )

    [10] => stdClass Object
        (
            [hour] => 10
            [views] => 0
        )

    [11] => stdClass Object
        (
            [hour] => 11
            [views] => 3
        )

    [12] => stdClass Object
        (
            [hour] => 12
            [views] => 0
        )

    [13] => stdClass Object
        (
            [hour] => 13
            [views] => 0
        )

    [14] => stdClass Object
        (
            [hour] => 14
            [views] => 0
        )

    [15] => stdClass Object
        (
            [hour] => 15
            [views] => 0
        )

    [16] => stdClass Object
        (
            [hour] => 16
            [views] => 0
        )

    [17] => stdClass Object
        (
            [hour] => 17
            [views] => 0
        )

    [18] => stdClass Object
        (
            [hour] => 18
            [views] => 0
        )

    [19] => stdClass Object
        (
            [hour] => 19
            [views] => 0
        )

    [20] => stdClass Object
        (
            [hour] => 20
            [views] => 0
        )

    [21] => stdClass Object
        (
            [hour] => 21
            [views] => 0
        )

    [22] => stdClass Object
        (
            [hour] => 22
            [views] => 0
        )

    [23] => stdClass Object
        (
            [hour] => 23
            [views] => 0
        )

)