0

I am trying to select records from a MySQL table that are between 1 and 2 weeks old The table has a date column. There is a date that is 10 days old but the query is ignoring it. Can you help please? Here is the query I am using:

$week2 = mysql_query("SELECT * FROM apps WHERE (user = '" 
.mysql_real_escape_string($_POST['username']) . "') and date BETWEEN ( DATE_SUB( NOW(), 
INTERVAL 2 WEEK), DATE_SUB( NOW(), INTERVAL 1 WEEK )");

If I make the date less than a week old, I can select the record no problem with:

$week1 = mysql_query("SELECT * FROM apps WHERE (user = '" .
mysql_real_escape_string($_POST['username']) . "') and date > NOW() - INTERVAL 1 WEEK
ORDER BY id DESC limit 1");
Kevmeister
  • 147
  • 1
  • 1
  • 12
  • 1
    **DO NOT USE** `mysql_query` in new applications. It's dangerous by default unless you use `mysql_real_escape_string`, where if you forget even one parameter, you can get destroyed. [Use PDO or mysqli](http://bobby-tables.com/php) to keep yourself safe. – tadman Aug 02 '12 at 14:57

1 Answers1

0

You're using BETWEEN incorrectly.

Try

DATE BETWEEN x AND y

You can pass in a string as the dates also:

<?php
    $startDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 14, date('Y')));
    $endDate = date("Y-m-d", mktime(0, 0, 0, date('m'), date('d') - 7, date('Y')));

    $query = "... AND DATE BETWEEN '" . $startDate . "' AND '" . $endDate . "'";

Also, to echo tadman's comment, you should stop using mysql_* functions. They're being deprecated. Instead use PDO or mysqli. If you're not sure which one to use, read this SO article.

Community
  • 1
  • 1
Matt
  • 6,993
  • 4
  • 29
  • 50