How to mount an SQL to display grouped content per month?

Asked

Viewed 268 times

1

I need to mount an SQL to group information per month within Bootstrap formatted columns. Come on:

<div class="container">
    <div class="row">
        <div class="col-md-3">
            <h2 class="eventos_mes">Maio</h2>
            <p>Primeiro Evento Título</p>
            <p>Segundo Evento Título</p>
            <p>Terceiro Evento Título</p>
            <p>Quarto Evento Título</p>
        </div>      
        <div class="col-md-3">
            <h2 class="eventos_mes">Junho</h2>
            <p>Primeiro Evento Título</p>
            <p>Segundo Evento Título</p>
            <p>Terceiro Evento Título</p>
            <p>Quarto Evento Título</p>
        </div>      
        <div class="col-md-3">
            <h2 class="eventos_mes">Julho</h2>
            <p>Primeiro Evento Título</p>
            <p>Segundo Evento Título</p>
            <p>Terceiro Evento Título</p>
            <p>Quarto Evento Título</p>
        </div>
        etc ...
    </div>
</div>

I don’t know how I could mount an SQL that groups these events by month separating them with Bootstrap formatting by columns.

For this example, consider that there are only three columns:

  • ID
  • Title
  • Date (mysql format)

Thank you

php mysql sql query

  • Put the information that is currently on the table.

  • Since it’s a learning title @Rafaelacioly, you can consider ID, title, date(mysql format) ... all other fields of each record I pull after I learn the base. Thank you.

  • You can make a database, and filter by month and with it make a select searching all the records of that month, later just display...

2 answers

3

Your situation does not require any particular query. Most of the implementation is at the time of drawing the tables.

Let’s assume you made a very simple query, sorted by date:

SELECT id, titulo, data
FROM evento
ORDER BY data ASC

At the time we draw the table, we should insert a new row into the table every time the month changes from the previous month.

<?php

class Evento
{
    public $id;
    public $data;
    public $titulo;

    public function __construct($id, $data, $titulo)
    {
        $this->id     = $id;
        $this->data   = $data;
        $this->titulo = $titulo;
    }
}

$eventos = [
    new Evento(1, new \DateTime('2015-01-26'), 'Titulo #1'),
    new Evento(1, new \DateTime('2015-01-31'), 'Titulo #2'),
    new Evento(1, new \DateTime('2015-03-02'), 'Titulo #3'),
    new Evento(1, new \DateTime('2015-05-04'), 'Titulo #4'),
    new Evento(1, new \DateTime('2015-05-08'), 'Titulo #5'),
    new Evento(1, new \DateTime('2015-08-01'), 'Titulo #6'),
    new Evento(1, new \DateTime('2015-09-14'), 'Titulo #7'),
    new Evento(1, new \DateTime('2015-09-19'), 'Titulo #8'),
    new Evento(1, new \DateTime('2015-11-10'), 'Titulo #9')
];

?>

<html>
<body>
<?php

echo '<table><tr><td>' . PHP_EOL;
$mesAnterior = null;
foreach ($eventos as $evento) {
    $mesAtual = $evento->data->format('F');
    if ($mesAtual != $mesAnterior) {
         echo '</td></tr><tr><td>' . $mesAtual . '</td><td>' . PHP_EOL;
    }
    echo '<p>' . $evento->titulo . '</p>' . PHP_EOL;
    $mesAnterior = $mesAtual;
}
echo '</td></tr></table>' . PHP_EOL;

?>
</body>
</html>

Output:

<html>
    <body>
        <table>
            <tr>
                <td>January</td>
                <td>
                    <p>Titulo #1</p>
                    <p>Titulo #2</p>
                </td>
            </tr>
            <tr>
                <td>March</td>
                <td>
                    <p>Titulo #3</p>
                </td>
            </tr>
            <tr>
                <td>May</td>
                <td>
                    <p>Titulo #4</p>
                    <p>Titulo #5</p>
                </td>
            </tr>
            <tr>
                <td>August</td>
                <td>
                    <p>Titulo #6</p>
                </td>
            </tr>
            <tr>
                <td>September</td>
                <td>
                    <p>Titulo #7</p>
                    <p>Titulo #8</p>
                </td>
            </tr>
            <tr>
                <td>November</td>
                <td>
                    <p>Titulo #9</p>
                </td>
            </tr>
        </table>
    </body>
</html>

  • Dear Rodrigo, can show me this structural because I do not have much experience in object orientation. Where I was most doubtful was in $events

  • $eventos is a list of objects, each simulating a row of your database. If you do the search using PDO or mysqli_query will probably receive an array of arrays instead, but the implementation is similar.

  • Dear Rodrigo, thank you for your help so far. You can take a look at the continuation of the question please at http://answall.com/questions/88090/porque-o-bootstrap-está-criando-uma-nova-coluna-para-o-secondtitle

  • Add to this question, don’t ask another question. :)

2

$consulta = mysql_query("SELECT * FROM tabela GROUP BY MONTH(data)");

    while($row=mysql_fetch_array($consulta)){
        $consulta_mes = mysql_query("SELECT * FROM tabela WHERE month(data) = month('".$row['data']."')");
        echo "Mês ".$mes;
        while($row_mes=mysql_fetch_array($consulta_mes)){
            echo "Listagem de todos os registros agrupados neste Mês";
        }
    }

Something more or less like this, will you adapt correctly to your reality.

  • André, I’m floating in this variable month. Where did he come from, where was he declared? Thank you

  • It is symbolic, you will have to make a function to search for the month and print which is the month corresponding to the return of the Row.. With this you will display ... Name of Month .... record 1, record 2 record 3.. etc

Browser other questions tagged php mysql sql query

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