Problem displaying array

Navigation
-1

I have a logic problem to display the data in an array.

It is the following:

I have a function, which returns me the result of registrations per UF per day. In this step 2 parameters, an array with the dates and an array with the states.

Mounting the parameters to pass to the function. 

$uf = array('sp','rj');

$d1 = '2015-09-15';
$d2 = '2015-09-17';
$timestamp1 = strtotime( $d1 );
$timestamp2 = strtotime( $d2 );

while ( $timestamp1 <= $timestamp2 ) {
   $data_mod[] = date( 'Y-m-d', $timestamp1 ) . PHP_EOL; // data_mod vira um array com as data entre $d1 e $d2
   $timestamp1 += 86400;
}

Function: 

function grafico_cadastro_por_dia($conexao,$uf,$data_mod){
$lista = array();
$qtd_dias = count($data_mod);
$qtd_uf = count($uf);

for($i = 0; $i <= $qtd_uf; $i++) {
if( $uf[$i] ) {  $where[] = " uf = '{$uf[$i]}'"; } //monto o implode para o array de UF
}

for($i = 0; $i <= $qtd_dias; $i++) {
if( $data_mod[$i] ) {  $where2[] = " dataCadastro = '{$data_mod[$i]}'"; } //monto o implode para o array de datas
} 

$query = "SELECT dataCadastro,uf,sum(cadastros) FROM tblCadastros where (".implode( ' or ',$where2 ).") AND (".implode( ' or ',$where ).") group by 1,2";
   $sql = mysql_query($query,$conexao);
   while($row = mysql_fetch_assoc($sql)){
   $lista[] = $row;
 } 
return $lista;
}

On return of the function, I get the following data. 

$funcao = grafico_cadastro_por_dia($conexao,$uf,$data_mod);

for($i = 0; $i <= count($funcao); $i++){
  echo  '<br>';
 print_r($funcao[$i]);
}

Resultado: 

Array ( [dataCadastro] => 2015-09-15 [uf] => SP [sum(cadastros)] => 36 )
Array ( [dataCadastro] => 2015-09-15 [uf] => RJ [sum(cadastros)] => 9 )
Array ( [dataCadastro] => 2015-09-16 [uf] => SP [sum(cadastros)] => 19 )
Array ( [dataCadastro] => 2015-09-16 [uf] => RJ [sum(cadastros)] => 8 )
Array ( [dataCadastro] => 2015-09-17 [uf] => SP [sum(cadastros)] => 14 )
Array ( [dataCadastro] => 2015-09-17 [uf] => rj [sum(cadastros)] => 2 )

As you can see, the function returns 2 times the date because it has more than one state to display the value.

If I pass 3 states in the array, it would display 3 times the same date.

So far so good.

The problem starts now, since I'm going to put those results on a graph.

And I can not print the results in the order the chart asks for.

You would have to display in that order: 

[$data, $total "uf SP" , $total "uf RJ"]
['2015-09-15', 36 , 9],// 36 é o valor de cadastro de SP no dia 15 e 9 o valor de cadastros de RJ no dia 15
['2015-09-16', 19 , 8],

and so on.

But I have already sketched everything I can imagine, but I could not display the data that way.

Can anyone help me?

    
asked by anonymous 28.09.2015 / 15:47

1 answer

1

You are traversing many arrays in order to mount a query to get the values by date of the records made in that UF, which is the SQL tool itself.

We have Between that is used for comparisons.

In this example I will show you how to get all the records made between a date period and group by the date of the registration and uf.

So I can return all the records made in x UF to Y day, mounting the array you need.

In this query I will use MySQL DATE_FORMAT , because I'm going to start from the beginning that your dataCadastro field has the registration schedule, if I do not format this date I will group the records by dia/mês/ano hora:minuto:segundo which is not what you need, you only need dia/mês/ano .

Just run this query instead of this whole code: 

SELECT 
DATE_FORMAT('dataCadastro', '%Y-%m-%d') AS 'dataCadastro',
uf,
SUM(cadastros) AS 'Cadastros'
FROM
  tblCadastros
WHERE
  dataCadastro BETWEEN 2015 - 09 - 15 AND 2015 - 09 - 17
GROUP BY DATE_FORMAT('dataCadastro', '%Y-%m-%d'), 'uf'

Only perform this query instead of this whole code that you have done. The result of this you can mount a dynamic array something like this: 

Array
(
    [2015-09-15] => Array
    (
        [SP] => 36
        [RJ] => 9
    )
)

See in the ideon

How would it end: 

$query = 'SELECT DATE_FORMAT('dataCadastro', "%Y%m%d") as 'dataCadastro', uf, sum(cadastros) as Cadastros FROM tblCadastros where dataCadastro BETWEEN '. $d1 .' and '. $d2 .' GROUP BY 'dataCadastro', 'uf'';
$sql = mysql_query($query,$conexao);

while($row = mysql_fetch_assoc($sql))
{
    $lista[$row['dataCadastro']] = [
        $row['uf'] => $row['Cadastros']
    ];
}

Questions? See how you reduced all of your code?

This way you have an array mounted with the information you want to assemble the graph, so just go through it.

Edit 

$lista = array();

while($row = mysql_fetch_assoc($sql))
{
   if(!isset($lista[$row['dataCadastro']]))
   {
    $lista[$row['dataCadastro']] = array(
        $row['uf'] => $row['Cadastros']
    );
   }
   else
   {
      $lista[$row['dataCadastro']] += array(
        $row['uf'] => $row['Cadastros']
      );
   }
}

See it working

    
28.09.2015 / 19:10
Relationship of tables using FK's [closed] Python code error [closed]