Balance by date in mysql

Question

Balance by date in mysql

Navigation

#1 by (1 votes)
#2 by (1 votes)

-1

I have a table requests and would like to return the following situation. select all the requests of an idCliente of the current month adding the values of the same ones. type select * from pedidos where idCliente = id and dataPedido (the month of search type 10/2018) sum(valortotalPedido) I'm not getting because the date field in mysql is as follows 2018-10-01 22:02:01 ie standard format. I do not know if I was clear but thank you for helping me.

php mysql date

asked by anonymous 24.10.2018 / 04:30

2 answers

Endpoint to send document by email Rename a table column with: nth-of-type

score 1 · Answer 1

Get the date you use Day / Month / Year convert to Year / Month / Day, get the year and month that the date provides and place in the query.

// Data recebida Dia/Mês/Ano
$data       = '01-11-2018 22:02:01';
// Converta a data para Ano/Mes/Dia
$data       = date('Y-m-d G:i:s', strtotime($data));
// Pegue o Mês da data
$mes        = date('m', strtotime($data));
// Pegue o Ano da data
$ano        = date('Y', strtotime($data));
$query  = "SELECT * FROM $this->table WHERE MONTH(data) = ':MES' AND YEAR(data) = ':ANO'";

Change the date format:

date('Y-m-d G:i:s', strtotime('01-10-2018 22:02:01'));

Return:

2018-10-01 22:02:01

More information: date strtotime

score 1 · Answer 2

To return the sum of the values per client:

$mes = date('m');
$ano = date('Y');

SELECT sum(valortotalPedido) FROM pedidos
WHERE idCliente = $id and Month(dataPedido) = $mes and Year(dataPedido) = $ano

As the query uses the sum aggregation function, it will not be possible to return the list of requests consistently, as the field "request_value" will be grouped.