0
Notice now that for my question, I will use only focused data for the problem, without having to put all the fields of a table and the whole HTML body. It took me a long time to elaborate on this question, but I’m sure it was well explained.
I have the table cde_venda_detail with all sales made from the company. This table has the column spawn, indicating the date of the sale.
Example:
| id | preco | criadoem |
| 1 | 199.99 | S:27. 29/06/2020 às 11:40 |
| 2 | 151.00 | S:27. 02/07/2020 às 13:20 |
| 3 | 19.99 | S:27. 05/07/2020 às 18:00 |
| 4 | 120.00 | S:28. 06/07/2020 às 09:00 |
| 5 | 12.50 | S:28. 06/07/2020 às 10:10 |
The column is of the type sweep, having the week, day, month, year and time of the sale: date('\S:W\. d/m/Y \à\s H:i')
I use 5 different filters in the HTML table:
- Total
- Daily
- Weekly
- Monthly
- Yearly
This is my current code (Working):
$dia = date('d/m/Y');
$semana = date('\S:W\.');
$mes = date('m/Y');
$ano = date('Y');
$filtro = 0;
if ($filtro == 0) {
$sql = "SELECT * FROM cde_venda_detalhe";
} else if ($filtro == 1) {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$dia%'";
} else if ($filtro == 2) {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$semana%' AND criadoem LIKE '%$ano%'";
} else if ($filtro == 3) {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$mes%'";
} else if ($filtro == 4) {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$ano%'";
}
$data = filter_input_array(INPUT_POST, FILTER_DEFAULT);
if ($data['total'] == 'Total') {
$sql = "SELECT * FROM cde_venda_detalhe";
}
if ($data['diaria'] == 'Diaria') {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$dia%'";
}
if ($data['semanal'] == 'Semanal') {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$semana%'";
}
if ($data['mensal'] == 'Mensal') {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$mes%'";
}
if ($data['anual'] == 'Anual') {
$sql = "SELECT * FROM cde_venda_detalhe WHERE criadoem LIKE '%$ano%'";
}
<form method="post" enctype="multipart/form-data">
<button name="total" value="Total" type="submit"> Total</button>
<button name="diaria" value="Diaria" type="submit"> Diária</button>
<button name="semanal" value="Semanal" type="submit"> Semanal</button>
<button name="mensal" value="Mensal" type="submit"> Mensal</button>
<button name="anual" value="Anual" type="submit"> Anual</button>
</form>
<table>
<thead>
<tr>
<th>Id da Venda</th>
<th>Preço da Venda</th>
<th>Data da Venda</th>
</tr>
</thead>
<tbody>
<?php foreach ($pdo->query($sql) as $row) { ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['preco']; ?></td>
<!--Por questão de estética, não exibo a semana na tabela-->
<td><?php echo substr($row['criadoem'], 5); ?></td>
</tr>
<?php } ?>
</tbody>
</table>
However, I would like this filter without always need to send a form, IE, in javascript, however javascript is not my strong.
Thank you in advance.
Man, it got hard to understand the context and how this consultation is being conducted. Why are you calling the database that way? Why not use an ORM and an API to handle this data? Another point is, because in the structure of your database a date column is in string format?
– M. Bertolazo
Initially this date was only for information, but then I needed to use it for consultations, in this case this question above. Although it is working, I know it is not the most correct way, I still need to learn more, by the way I have no college, only a Technical Computer Course that was held next to high school, And let’s say they didn’t care much about development, but they cared about hardware and networks. Anyway, those who were interested in programming had to work hard to learn this part, which is my favorite
– Afuro Terumi
I still put the date in the correct format, but I needed this part of the week, as I could not identify the week through the date, and as there is no way to enter the week number of the date, had two options: or add a column only for the week of sale, or leave it string-like, containing the week (which is now). But don’t worry, the only table that contains date that is as string is this.
– Afuro Terumi
Nice guy, good to see that you are chasing. There is a very serious problem in what you are doing: the query is in Javascript. Your query must be in the server part, because what is in the client, the user can modify. Thus allowing an injection of SQL into your database. I recommend you reformulate this code so that instead of 5 buttons, you have a Dropdown with the options, and the server treat as the value the query build
– M. Bertolazo