Listing data by date

Asked

Viewed 209 times

1

I have the following structure:

inserir a descrição da imagem aqui

I want to list all products by date. Like, date such products are in a table, already another date is in another table, and so on... All this on the same page.

php
  • 1

    First you can select using GROUP BY data, and then assemble a new select to pick products only from that date... displaying as follows: DATA X --- products | DATA Y --- products

1 answer

1

The following way tries to minimize the number of queries to the database, leaving only two queries and for the rest PHP is responsible.

The logic is nothing more than to get the dates that will be listed sorted, and get the list of all products sorted by the dates as well. Then scroll through the date list, and for each date you scroll through the product list removing each item after presenting, until the date changes. When changing the table is finished and moves to the next date.

<?php
// Obtendo as compras ordenadas pela data
$sqlCompras = '
    SELECT
        DATE_FORMAT(c.`data`, '%d-%m-Y') as "data",
        c.`produtoNome`,
        c.`preco`
    FROM `compras` as c
    ORDER BY
        DATE_FORMAT(c.`data`, '%d-%m-Y') ASC,
        c.`produtoNome` ASC
';
$resCompras = $conexao->prepare($sqlCompras);
$resCompras->execute();
$compras = $resCompras->fetchAll();

// Obtendo todas as datas disponíveis
$sqlDatas = '
    SELECT
        DATE_FORMAT(c.`data`, '%d-%m-Y') as "data"
    FROM `compras` as c
    GROUP BY BY c.`data`
    ORDER BY
        DATE_FORMAT(c.`data`, '%d-%m-Y') ASC,
';
$resDatas = $conexao->prepare($sqlDatas);
$resDatas->execute();
$datas = $resDatas->fetchAll();
?>

<?php foreach ($datas as $data) { ?>
<table>
    <caption><?php echo $data['data'] ?></caption>
    <thead>
        <tr>
            <th scope="col">Nome do produto</th>
            <th scope="col">Preço</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($compras as $key => $compra) { ?>
            <?php
            // Percorre os produtos até encontrar uma data diferente,
            // quando encontrar, quebra o laço de repetição e passa para a
            // próxima data.
            if( $compra['data'] != $data['data']) {
                break;
            } else {
            ?>
                <tr>
                    <td><?php echo $compra['produtoNome'] ?></td>
                    <td><?php echo $compra['preco'] ?></td>
                </tr>
                <?php unset($compras[$key]) ?>
            <?php } ?>
        <?php } ?>
    </tbody>
</table>
<?php } ?>

Browser other questions tagged php

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