Error comparing PHP-Mysql dates

Question

I have the following code:

HTML:

<input name="dataInicio" type="text" class="form-control datepicker" placeholder="Data" maxlength="10" autocomplete="off" />
<input name="dataFim" type="text" class="form-control datepicker" placeholder="Data" maxlength="10" autocomplete="off" />

Mysql

`date` DATE NOT NULL,

PHP

$dataInicio = (isset($_POST['dataInicio'])) ? '2017-09-25' : date("Y-m-d", strtotime($_POST['dataInicio']));
$dataFim = (isset($_POST['dataFim'])) ? date("Y-m-d") : date("Y-m-d", strtotime($_POST['dataFim']));

And finally the test I’m doing to return the interval of days I need:

PHP

SELECT date,time,resultCode,hostname,user,sites,qt_acessos,bytes
                FROM tblResumo
                WHERE 
                    date >= '$dataInicio' AND 
                    date <= '$dataFim'

But I have the return of ALL records, regardless of the date informed in my application form! And what I don’t understand is that by checking in the POST, the values reported for the date range are there correctly. I am printing the values as JSON if this information is useful. Where am I wrong? I anticipate my thanks.

Answer 1

There is a field of the type date html:

<input type="date">

This will standardize the information passed in the field, rather than being valid later if it is all numbers for example.

In the SELECTyou can use the SQL command BETWEEN:

This command takes values between past parameters, such as dates. Example:

SELECT * FROM tabela WHERE data BETWEEN '2017-01-01' AND '2017-01-31';

Obs: Exists in Jquery UI a function called datepicker where you can optimize the whole date field.

Answer 2

There is a problem in your definition of variables $dataInicio and $dataFim. Ternary operator returns '2017-09-25' and date("Y-m-d") case exists data in item $_POST correspondent.

I imagine what you want is to fill with '2017-09-25' or date("Y-m-d") (today) case does not exist data in the array $_POST. Therefore, using !isset():

$dataInicio = (!isset($_POST['dataInicio'])) ? '2017-09-25' : date("Y-m-d", strtotime($_POST['dataInicio']));
$dataFim = (!isset($_POST['dataFim'])) ? date("Y-m-d") : date("Y-m-d", strtotime($_POST['dataFim']));