Mysqli Prepare with loop for Select

Question

Mysqli Prepare with loop for Select

Navigation

#1 by (1 votes)

1

I am trying to make a SELECT using MySQLi and PREPARE in FOR but not working. It is always returning a single zero.

Application code:

if( $teste = $mysqli->prepare("SELECT COUNT(*) AS TOTAL_CONTATOS, 'data' AS DATA_CONTATO FROM 'contatos' WHERE 'data' >=? AND 'data' <=?") )
{

    $mes = date("m");
    $ano = date("Y");

    $data_start = "01-".$mes."-".$ano;

    $tempo_start = strtotime($data_start);
    $tempo_end = strtotime("+1 month", $tempo_start);

    for( $i = $tempo_start; $i < $tempo_end; $i += 86400 )
    {
        $teste->bind_param( 'ss', date('Y-m-d', $i), date('Y-m-d', $tempo_end) );
        $teste->execute();
        $teste->bind_result( $totalcontato, $data_contato);
        $teste->store_result();
    }

    while( $teste->fetch() )
    {
        echo $totalcontato ." | ". $data_contato;
        echo "<br>";
    }
}

php mysql sql mysqli

asked by anonymous 13.01.2016 / 16:34

1 answer

Change column type of PostegreSQL Database ImgSelect: MediaStream stop is not a function

score 1 · Accepted Answer

With the help of @rray that suggested putting while inside for and some adjustment I got the result I wanted.

Follow SCRIPT so you can help someone with the same problem:

if( $teste = $mysqli->prepare("SELECT COUNT(*) AS TOTAL_CONTATOS FROM 'contatos' WHERE 'data' >=? AND 'data' <=?") )
{

    $mes = date("m");
    $ano = date("Y");

    $data_start = "01-".$mes."-".$ano;

    $tempo_start = strtotime($data_start);
    $tempo_end = strtotime("+1 month", $tempo_start);

    for( $i = $tempo_start; $i < $tempo_end; $i += 86400 )
    {
        $teste->bind_param( 'ss', date('Y-m-d', $i), date('Y-m-d', $i) );
        $teste->execute();
        $teste->bind_result($totalcontato);
        while( $teste->fetch() )
        {
            echo $totalcontato ." | ". date('Y-m-d', $i);
            echo "<br>";
        }
    }

}

OUTPUT

0 | 2016-01-01
0 | 2016-01-02
4 | 2016-01-03
4 | 2016-01-04
6 | 2016-01-05
6 | 2016-01-06
0 | 2016-01-07
0 | 2016-01-08
4 | 2016-01-09
7 | 2016-01-10
6 | 2016-01-11
5 | 2016-01-12
5 | 2016-01-13
0 | 2016-01-14
0 | 2016-01-15
8 | 2016-01-16
10 | 2016-01-17
9 | 2016-01-18
6 | 2016-01-19
0 | 2016-01-20
0 | 2016-01-21
0 | 2016-01-22
5 | 2016-01-23
8 | 2016-01-24
7 | 2016-01-25
6 | 2016-01-26
0 | 2016-01-27
0 | 2016-01-28
0 | 2016-01-29
0 | 2016-01-30
0 | 2016-01-31

With this I have reached the total number of contacts per day of the current month, even if the value is zero, dynamically. I hope to help someone.