break date in 3 columns in a mysql query

Question

I have a system where I need to select any date in a calendar, so I need to return all the month records of that date, for example: I have 4 dates, 01/08/2017, 06/08/2017, 14/09/2017, 17/10/2017. Suppose I clicked on the date "01/08/2017" and mine query must return all dates recorded in the month of August. In this case the return has to be 01/08/2017 and 06/08/2017.

Follow the code of how I tried to do.

if (isset($_POST['calendar']) AND $_SERVER['REQUEST_METHOD'] == 'POST'){

    $date = $_POST['date'];

    $cashier = listarColunas("cashier", " DATE_FORMAT(cas_day,'%d/%m/%Y') as 
    cas_day", "WHERE MONTH(cas_day) = '10'");

The field cas_day is where I store the date, but I needed to break it into 3 parts, because that way did not work.

Note: This is my function

listarColunas($tabela,  $colunas, $parametros = null)

Answer 1

If I understand correctly, you want to break the date in columns to facilitate the consultation by month?

Instead you can convert your date string into Mysql date using STR_TO_DATE and compare oranges with oranges in the clause WHERE =)

$date = "01/08/2017"; //Pela pergunta esse deve ser o formato do $_POST['date']
$cashier = listarColunas("cashier", " DATE_FORMAT(cas_day,'%d/%m/%Y') as 
cas_day", "WHERE MONTH(cas_day) = MONTH(STR_TO_DATE('$date','%d/%m/%Y'))");

Here is an example of the query itself in SQL Fiddle: http://sqlfiddle.com/#! 9/e53323/8/0

Answer 2

I am considering that your $_POST['date'] is a string with a date

if (isset($_POST['calendar']) AND $_SERVER['REQUEST_METHOD'] == 'POST'){

$date = new DateTime($_POST['date']);
$mes = $date->format('m');

$cashier = listarColunas("cashier", "DATE_FORMAT(cas_day,'%d/%m/%Y')", "WHERE DATE_FORMAT(cas_day,"%c") = ".$mes);