how to consult by month?

Question

In the BD table I have a column "day" type "date" keeping "yyyy-mm-dd", I’m trying to make a query that returns me the current month, I’ve tried many ways and nothing.

<?php session_start(); date_default_timezone_set('America/Sao_Paulo'); $d = date('d'); $m = date('m'); $y = date('Y'); $query_cx = "SELECT dia FROM caixa WHERE MONTH(data)='$m' ORDER BY id DESC"; $result_cx = mysqli_query($conectar, $query_cx); while ($linhas_cx = mysqli_fetch_assoc($result_cx)){ echo "$linhas_cx['valor']; } ?>

the second part are doubts, thinking that the year is ending and that this consultation will always return me the current month;

1- this consultation will end up returning me the current month and the same month of the previous year?

2- as I would do to search some previous month if the year will be different?

Answer 1

(sic) I am trying to make a query that will return me the current month

Solution (assuming your db is Mysql or Mariadb):

SELECT `dia` FROM `caixa` WHERE `data` >= '2018-12-01' AND `data` <= '2018-12-31';

In the PHP you will only need to replace the month on query, staying like this (following your example):

<?php session_start();
    date_default_timezone_set('America/Sao_Paulo');
    //$d = date('d');
    //$m = date('m');
    //$y = date('Y');
    //$query_cx = "SELECT dia FROM caixa WHERE MONTH(data)='$m' ORDER BY id DESC";
    $query_cx = "SELECT `dia` FROM `caixa` WHERE `data` >= '".date('Y-m')."-01' AND `data` <= '".date('Y-m')."-31';";
    $result_cx = mysqli_query($conectar, $query_cx);
    while ($linhas_cx = mysqli_fetch_assoc($result_cx)){
       // echo "$linhas_cx['valor']; // PQ USAR ASPAS AQUI (e ainda falta fechar)?
       echo $linhas_cx['valor'];
    }

1- this consultation will end up returning me the current month and the same month of the previous year?

No, because we declare the year in the consultation.

2- as I would do to search some previous month if the year will be different?

Just change the query:

$query_cx = "SELECT `dia` FROM `caixa` WHERE `data` >= '".date('Y-m', strtotime('-1 month'))."-01' AND `data` <= '".date('Y-m', strtotime('-1 month'))."-31';";

Recommended reading:

Manipulating dates with PHP

Answer 2

A simple way to do this is by using only sql itself

SELECT dia FROM caixa WHERE MONTH(data)= MONTH(NOW()) ORDER BY id DESC