CODEWITHSUNDEEP
phpsqlmysql
holian
holian

Reputation: 755

SQL query - problem with interval

I have a table with many two columns: id, mydate

I have 10 record in this table. 8 record have ID=1111 and two ID=2222

The two rows in the database:

id         mydate
2222       2011-02-10 09:25:02  
2222       2011-02-09 18:44:08

When the page is load, I want to check my table specifically for an ID and if the difference between the DATE of this IDs and the current DATE is bigger then X days then I want to update that record.

I try something like this:

$X=1;

$query = "SELECT id FROM testtable WHERE id='2222' AND mydate > DATE_SUB(NOW(), INTERVAL $X DAY)";
$result = mysql_query($query) or die("something went wrong..");
$test= array();
if (mysql_num_rows($result) > 0) {
    $i = 0;
    while($test[$i] = mysql_fetch_array($result)) {
     $i++;

    }
  }
  echo "the number of rows is:".$i;

But with this $i is empty.If i change $X=2 then i got 2 in echo. I don't understand. The current server date is: Friday 11th of February 2011 11:41:51

Upvotes: 0

Views: 338

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Instead of DATE_SUB(NOW(), use DATE_SUB(CURDATE() to go to midnight today.

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

Both rows in your table are more than a day before the current time, so naturally they won't be returned when you ask for rows that are less than a day old. I think your mistake is that you (the person, not your code) did not look at the time part of the data so expected something else.

Upvotes: 0

Related Questions