Mysqli Prepare with Loop for Select

Asked

Viewed 99 times

1

I’m trying to make a SELECT using MySQLi and PREPARE in a FOR but not working. 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 database mysqli

  • @Bacco It was to return every day of the month with the values. Even if the value is 0.

  • what’s in your $test variable? try removing it and see what the result is...

  • Throw the while into the for, it should be something different

  • @rray Printed some data. Thank you, with this I made some changes and got as I wanted. I will answer the question.

  • Nice that you managed to solve :D. Just for the record, I hate bind_result()

  • @rray you use what in the place or not PREPARE? 'Cause, look it’s really boring, it has to be worth the performance.

  • When possible use get_result() see the difference, but you need to stay smart get_result() depedende mysqlnd, if it is not installed on your production host, only the bind_result() even.

Show 2 more comments

1 answer

1


With the help of @rray who suggested putting the while within the for and some adjustment came to the result I desired.

Follows the SCRIPT so I 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 reached the total of contacts per day of the current month, even if the value is zero, dynamically. I hope to help someone.

Browser other questions tagged php mysql sql database mysqli

You are not signed in. Login or sign up in order to post.