Registration report with error in method return

Question

Registration report with error in method return

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a problem that I have not been able to resolve for more than a week.

I want to do a report of entries for a particular site.

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

Previously, I would make a query without using a method, only the mysql_query() function of PHP where I would capture the data in the MySQL database would list the records with the while loop on the screen. However, I want to improve this, as this system requires me to use a method again and again, I want to adapt it using a method that serves as an instrument for reusing the code.

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

The structure of the code I'm breaking the head:

$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);

    }

}

Displaying the $funcao[0] variable should list all cities in the state. But, it's just returning me 1 result. How could you solve this problem?

php mysql

asked by anonymous 18.09.2015 / 21:06

2 answers

1

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

I've changed date to data_cadastro because date is a word reserved for MySQL and therefore will 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 does not make sense to pass the connection into the mysql_query() method. I adapted the method with the PSR-1 and PSR-2 standard.

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;
}

18.09.2015 / 21:31

Extract information from a text file with shell script and regular expressions How to capture and handle an Exception in Swift language

score 0 · Accepted Answer

Return all lines after the while you 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;
}