Problem when displaying array

Question

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

Is the following:

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

Setting 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 function, I receive 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 will put these results on a graph.

And I’m having trouble printing the results in the order the graph asks for.

You’d have to exhibit 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’ve sketched out all the ways I can imagine, but I couldn’t display the data that way.

Can someone help me?

Answer 1

You are going through many arrays to at the end assemble a query to get the values by date of the entries made in that UF, and own SQL tools for this.

We have the function Between which is used for comparisons.

In this example I will show how to take all records made between a date period and group by the registration date and Uf.

So I can return all entries made in x UF for Y day, mounting array you need.

In this query I will use the DATE_FORMAT Mysql, because I’m going to assume that your field dataCadastro have the registration time, if I do not format this date I will group the registrations by dia/mês/ano hora:minuto:segundo which is not what you need, you need just by dia/mês/ano.

Just run this query instead of all this 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`

Just run this query instead of all this code you did. The result of this you can mount a dynamic array more or less like this:

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

Look at the ideone

How final:

$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']
    ];
} 

Doubts? See how you’ve reduced all that code?

That way you have an array mounted with the information you want to mount the graph, then 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 in operation