Group content by month and day [closed]

1

Good afternoon Guys, I'm having a problem with grouping content in the following structure:

Month
Day

In the query I have two tables, "schedule" and "user". In the calendar table I have a field of type "date" that "should" group the content.

$data = date("Y-m-d");
$mes_atual = '';
$dia_atual = '';
$tbody = '';
$dados = Connection::select("Select agenda.id,u.nome as
     nome_pessoa,agenda.hora,agenda.compromisso,agenda.local,agenda.pessoa,
     agenda.data,DAYNAME(NOW()) AS dia, year(NOW()) AS ano,
     MONTHNAME(NOW()) AS mes from agenda inner join users u on 
    (agenda.pessoa = u.id) order by mes asc, dia asc, hora asc");

foreach ($dados as $reg) {
    if ($mes_atual != $reg['mes']) {
        $tbody.= '<tr><td colspan=4><h3>' . $reg['mes'] . '</h3></td></tr>';
        $mes_atual = $reg['mes'];
    }
    if ($dia_atual != $reg['dia']) {
        $tbody .= '<tr><td colspan=4><h5>' . $reg['dia'] . ', ' . $reg['dia'] . '</h5></td></tr>
<tr>
<td style="width:10%;"><b>Hora</b></td>
<td style="width:40%;"><b>Compromisso</b></td>
<td style="width:25%;"><b>Local</b></td>
<td style="width:25%;"><b>Pessoas</b></td>
</tr>';
        $dia_atual = $reg['dia'];
    }
    $tbody .= '
<tr>
<td style="width:10%;">' . $reg['hora'] . '</td>
<td style="width:40%;">' . $reg['compromisso'] . '</td>
<td style="width:25%;">' . $reg['local'] . '</td>
<td style="width:25%;">' . $reg['nome_pessoa'] . '</td>
</tr>                           
';
}
$html = str_replace('#TBODY#', $tbody, $html);
return $html;

The weird thing is that if I do not match the "schedule" and "user" tables, clusters work.

    
asked by anonymous 22.02.2016 / 21:17

1 answer

0

To do what you want in your Database :

Select 
  agenda.id,
  u.nome as  nome_pessoa,
  agenda.hora,
  agenda.compromisso,
  agenda.local,
  agenda.pessoa_id,
  agenda.data,
  DAYNAME(NOW()) AS dia, year(NOW()) AS ano, MONTHNAME(NOW()) AS mes 
FROM 
    agenda  inner join users u on (agenda.pessoa_id = u.id) 
    order by agenda.data asc, agenda.hora asc

If you look at just changed the last line, now my point of view would use the structure below:

SELECT
    USER.nome,
    AGENDA.id,
    AGENDA.hora,
    AGENDA.compromisso,
    AGENDA.local,
    AGENDA.pessoa_id,
    AGENDA.data
FROM 
    agenda as AGENDA,
    users as USER
ORDER BY 
    AGENDA.data ASC,
    AGENDA.hora ASC

In PHP you work the Month and Day detection functions and still can translate to the language you define:

<?php
   #garante a traducao
   setlocale(LC_ALL, 'pt_BR');
   #garante que vai ficar setado a data correta de acordo com a timezone
   date_default_timezone_set('America/Sao_Paulo');
   #http://php.net/manual/pt_BR/function.strftime.php
   $dia = strftime("%A");
   $mes = strftime("%B");
   echo "Hoje é <b>$dia</b> do mês de <b>$mes</b>";
?>

Give it a better search, use the right tools, it's worth it in the end.

    
23.02.2016 / 00:19