Group content per month and day

Asked

Viewed 69 times

1

Good afternoon Guys, I’m having trouble grouping content in the following structure:

Mes
Day

In the query I have two tables, "agenda" and "user". In the agenda 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 strange thing is that if I do not relate the tables "agenda" and "user", the groupings work.

php mysql

  • DAYNAME(NOW()) AS dia, year(NOW()) AS ano,&#xA; MONTHNAME(NOW()) AS mes What is the point of getting the current date and time in the query? Wouldn’t it be the name of the field instead of the NOW() ? I found this part of your code a little strange.

  • 1

    Our hesitation, that was the error of the code, ended up passing unnoticed. Thank you friend.

  • 1

    As you already solved the problem, I am voting to suspend your question as not reproducible error, to prevent the staff from posting answer thinking that the problem is open (this is neither a punishment nor a criticism of your posting, just a mechanism of organization of the site). But if you find any other code problems, feel free to ask some new questions, we’re here to help. If you prefer, you can post as an answer to your own question as you did to solve the problem, and mark it as accepted, so it is also a means to leave the complete post..

1 answer

0

To do what you want in yours 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 observe I 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 the PHP you work the detection functions of Month and Day and still break manages to make the translation to the language you set:

<?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/en/function.strftime.php
   $dia = strftime("%A");
   $mes = strftime("%B");
   echo "Hoje é <b>$dia</b> do mês de <b>$mes</b>";
?>

Gives a better search, use the right tools, ultimately worth it.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.