Check data in the database according to the date

Question

Query data in mysql in the last 3 days, ie display database data from day 15 until today (17/02/2014)

I’m wearing it like this:

$condicaoData = date('d/m/Y', strtotime("-3 days"));
$selecionaTabela = mysql_query("SELECT * FROM noticias
                   WHERE data >= '$condicaoData' ORDER BY id DESC")or die(mysql_error());

But is coming data of the month of January also, IE, the month is not counting, only the day.

Answer 1

Supposing data is in format DATE, DATETIME or TIMESTAMP, $condicaoData should be in format YYYY-MM-DD.

You can do two things: provide the date in the received format and use the function DATE_FORMAT Mysql or transform the received date into the format required by Mysql.

To use the function DATE_FORMAT:

$sql = "
     SELECT *
     FROM noticias
     WHERE data >= DATE_FORMAT('$condicaoData', '%d/%m/%Y')
     ORDER BY id DESC";

To transform the received format into Mysql format:

// caso você possa gerar a data no formato Y-m-d
$condicaoData = date('Y-m-d', strtotime("-3 days"));

// ou caso você já tenha a data no formato d/m/Y
$condicaoData = preg_replace(
    '/^(\d+)\/(\d+)\/(\d+)$/',
    '$3-$2-$1',
    date('d/m/Y', $condicaoData));

And then SQL would be:

$sql = "
    SELECT *
    FROM noticias
    WHERE data >= '$condicaoData'
    ORDER BY id DESC";

Another tip: consider using Prepared Statements, which is the safest option to protect your database against SQL type Injection attacks. So the query is cached in your database and the only things that change are the parameters - in this case, the dates of your query.

Answer 2

In the mysql dates are by default saved in format YYYY-MM-DD, so much for DATE, DATETIME or TIMESTAMP as can be seen in mysql documentation
In this case try the following code:

$condicaoData = date('Y-m-d', strtotime("-3 days"));
$selecionaTabela = mysql_query("SELECT * FROM noticias
                   WHERE data >= '{$condicaoData}' ORDER BY id DESC")or die(mysql_error());

Note: I entered "{}" into the variable $condicaoData because some servers do not correctly interpret a variable directly within a string (if misconfigured mainly) and this ensures that your variable will be read correctly.

Answer 3

You don’t even need to use the variable $condicaoData, using the Mysql NOW() function to capture the current moment and the DATE_SUB() function to subtract a certain date range. See the example below:

To ensure that you take from exactly the current time up to -3 days.

$query = "SELECT * FROM noticias ".
         "WHERE (data BETWEEN DATE_SUB(NOW(), INTERVAL 3 DAY) AND NOW()) ".
         "ORDER BY id DESC";
$selecionaTabela = mysql_query($query);

If you are sure that there are no records older than the date of the current moment, you can search as well:

$query = "SELECT * FROM noticias ".
         "WHERE data >= DATE_SUB(NOW(), INTERVAL 3 DAY)) ".
         "ORDER BY id DESC";
$selecionaTabela = mysql_query($query);

Mysql functions (w3schools.com):
- NOW()
- DATE_SUB()

Mysql functions (dev.mysql.com):
- NOW()
- DATE_SUB()

Answer 4

Try this MONTH = http://www.w3resource.com/mysql/date-and-time-functions/mysql-month-function.php

YEAR = http://www.w3resource.com/mysql/date-and-time-functions/mysql-year-function.php

$condicaoData = date('d/m/Y', strtotime("-3 days"));
$selecionaTabela = mysql_query("SELECT * FROM noticias
                   WHERE data >= '$condicaoData' AND MONTH(CURDATE())=MONTH(data) AND YEAR(CURDATE())=YEAR(data)  ORDER BY id DESC") or die(mysql_error());