Query data in the bank according to the date

3

Querying data in mysql in the last 3 days, that is, displaying data from the bank from day 15 to today (2/17/2014)

I'm using 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 data coming from January is also coming, ie the month is not counting, just the day.

    
asked by anonymous 17.02.2014 / 14:27

4 answers

2
Assuming data is in the format DATE , DATETIME or TIMESTAMP , $condicaoData must be in YYYY-MM-DD format.

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

To use the DATE_FORMAT function:

$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 most secure option to protect your database against SQL injection-type 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.

    
17.02.2014 / 14:28
1

No dates are saved in the format YYYY-MM-DD , either for DATE , DATETIME or TIMESTAMP as can be seen in the documentation from MySql
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 put "{}" in the variable $condicaoData because some servers do not correctly interpret a variable directly inside a string (if badly configured mostly) and this ensures that its variable is read correctly. >     

17.02.2014 / 14:33
0

Try this MONTH = link

YEAR = link

$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());
    
17.02.2014 / 14:50
0

You do not even need to use the $ condicodeData variable, 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: Home To ensure that it will pick up exactly from the current moment 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 with a date greater than the date of the current time, you can also search for:

$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 ()

    
17.02.2014 / 14:42