Error registration report on method return

Question

I’m having a problem I haven’t been able to solve in over a week.

I wish to make a registration report for a particular website.

In the report, I want to list the cities and their total registrations.

Previously, I would do a query without using a method, only the function mysql_query() PHP where I would capture the data in the Mysql database, list the records with the type loop while onscreen. However, I want to improve this, as this system requires me to use a method over and over again, I want to adjust it using a method that serves as an instrument for reusing the code.

However, I am not managing to make the result that is within the while(...) appear correctly in my method.

The structure of the code I’m cracking my head on:

$uf = $_POST['uf'] //aqui, $uf recebe 1 ou mais estados vai post

$x = count($uf);

for($i = 0 ; $i < $x; $i++){

    $funcao = cad_por_uf($uf[$i],$dataInicial,$dataFinal); // aqui ,passo o valor do return da funcao para a variavel $funcao;

    echo $funcao[0].'-'.$funcao[1];
}


function cad_por_uf($uf,$dataInicial,$dataFinal){

    $sql = mysql_query("SELECT sum(cadastros),uf,cidade FROM tblCadastros where data >= '$dataInicial' and data <= '$dataFinal' and uf = '$uf' and deletada = 0 group by (cidade)");
    while($row = mysql_fetch_array($sql)){
        $cidade = $row['cidade'];
                $cadastros= $row['sum(cadastros)'];                

                return array($cidade,$cadastros);

    }

}

When displaying the variable $funcao[0], should list all the cities in the state. But, he’s just returning me 1 result. How could solve this problem?

Answer 1

The correct in this case would be to do your query this way:

Obs: traded date for data_cadastre, because date is a reserved word of Mysql and will therefore return with error. Another detail is how to declare array. You declare in two ways: $array = []; (version 4.5 or higher) or $array = array();. Another thing is it makes no sense to pass the connection into the method of mysql_query(). I adjusted the method with the standard PSR-1 and PSR-2.

function cadPorUf($uf, $initialDate, $finalDate)
{

   $sql = "SELECT sum(cadastros) as total,
           uf,
           cidade FROM tblCadastros
           WHERE data_cadastro
           BETWEEN '$initialDate' and '$finalDate'
           AND uf = '$uf'
           AND deletada = 0
           GROUP BY cidade";
    //faça um upgrade para PDO (vamos colaborar para melhorar e não piorar os códigos)
    $query = mysql_query($sql);
    $list = array();
    while ($row = mysql_fetch_array($query)) {
        $list[] = $row;
    }
    return $list;
}

Answer 2

Return all rows after while do not need to create a new array, use an alias for the calculation.

function cad_por_uf($uf,$dataInicial,$dataFinal, $conexao){
    $consulta = "SELECT sum(cadastros) as cadastros, uf, cidade
                 FROM tblCadastros
                 WHERE data >= '$dataInicial' and data <= '$dataFinal' and uf = '$uf' and deletada = 0 group by (cidade)";

    $sql = mysql_query($consulta, $conexao);
    $lista = array();
    while($row = mysql_fetch_assoc($sql)){
        $lista[] = $row;
    }
    return $lista;
}