I will be able to do in my query a SELECT with a date range according to the current month

Question

I will be able to do in my query a SELECT with a date range according to the current month

Navigation

#1 by (2 votes)

1

I have the following query:

$analisis = "SELECT usuario, users.nombre AS 'nombre', users.email AS 
                    'email', SUM(monto) AS 'monto_total', 
                     COUNT( usuario ) AS 'num'
             FROM pedidos 
             INNER JOIN users ON (pedidos.usuario=users.idusuario) 
             WHERE status_pedido = 'ENTREGADO'
             GROUP BY 'usuario', 'monto', nombre, email 
             ORDER BY num DESC
             LIMIT 0 , $limit_end";

This query allows me to obtain a table with the following data:
Usuario, Nombre, Email, Cantidad de Pedidos y el Total
In the table Orders I have a column with the name date which is a timestamp. I would like to know if it is possible to filter the results by the ones corresponding to the current month, I have several declarations of reference variables to dates that allows me for other functions to execute particular things but I can not manage to give the instruction to my query that it also filters WHERE date is equal to the current month.

$start = time();
$fecha_act = date("y-m-d H:i:s",$start);
$fads = strftime("%A %d de %B del %Y");
$fecha_actual_sistema = strftime("%Y/%m/%d");
$fecha_sistema = date("Y/m/d");
$dia = "";
$mes = strftime("%B");
$mes_de_pago_actual = strftime("%B/%Y");
$mes_fecha_sistema = date("m/Y");

mysql sql php

asked by Jose M Herrera V 20.11.2018 в 16:02

source

1 answer

Error handling in Angular Enlarge Image when clicking

score 2 · Accepted Answer

You can do it directly in the statement mysql , for this you use date_format to compare the year and month of your date with the year and month of the current date. It is necessary to compare year and month so that it brings the present month of this year, otherwise it will bring this month for all the years represented in the table.

"SELECT usuario, users.nombre AS 'nombre', users.email AS 
                    'email', SUM(monto) AS 'monto_total', 
                     COUNT( usuario ) AS 'num'
             FROM pedidos 
             INNER JOIN users ON (pedidos.usuario=users.idusuario) 
             WHERE status_pedido = 'ENTREGADO'
             AND DATE_FORMAT(fecha, '%Y%m') = DATE_FORMAT(NOW(), '%Y%m')
             GROUP BY 'usuario', 'monto', nombre, email 
             ORDER BY num DESC
             LIMIT 0 , $limit_end";