my $data = qq(SELECT search, engine, time FROM searches WHERE DATE_SUB
++(CURDATE(),INTERVAL 7 DAY) <= time);

my $ref = $dbh->selectall_arrayref($data);
# For fun you can even check the number of rows returned
print "Total Searches: ". ($#{$ref} + 1) ."<br>\n";
for my $row (0 .. $#{$ref}) {
  print "$ref->[$row][0] $ref->[$row][1] on $ref->[$row][2]<br>\n";
}
[download]

This should print out all your data.

Update: I would also ensure that you are comparing like data. Ie, I usually compare time in UNIX_TIMESTAMP() format. Therefore I would change your query to be something like this:

my $data = qq(SELECT search, engine, time FROM searches WHERE UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 7 DAY)) <= UNIX_TIMESTAMP(time));

Eric

THAT GOT IT TO WORK!!!! THANK YOU!! THANK YOU!! THANK YOU!!

But what exactly was the problem? All the saved timestamps were in the same format of MySQL's NOW().

The reason it worked is because the way I did it was comparing UNIX_TIMESTAMP() format's as opposed to comparing "2006-08-06" < "2006-08-08". MySQL doesn't do comparison's like this to the best of my knowledge. Hence the reason I translated them both into UNIX_TIMESTAMP() format so it is comparing the number of seconds from epoch until NOW() or until NOW()-INTERVAL, etc.

To show more aptly.

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2006-08-10 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2006-08-10 15:41:19 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1155238891 |
+------------------+
1 row in set (0.00 sec)
[download]

This way, when I use the '<' or '>' to compare numbers (such as UNIX_TIMESTAMP()'s), it is consistant. As opposed to comparing CURDATE() and NOW() where one has a time value and one is just a date. That is how I remember things anyway.

Eric

Maybe, this happens because NOW() and CURDATE() return date in different formats?

Try

my $data = qq(SELECT search, engine, time FROM searches WHERE DATE_SUB
+(NOW(),INTERVAL 7 DAY) <= time);
[download]

I cannot test it now, but i think that it will produce the wanted output too.

---

     s;;Just-me-not-h-Ni-m-P-Ni-lm-I-ar-O-Ni;;tr?IerONim-?HAcker ?d;print

I don't think it can be my data because I'm just storing the time as a timestamp.

  my $data = qq(INSERT INTO searches (search, engine, time) values(?,?
+,NOW()));
  my $sth = $dbh->prepare($data);
  $sth->execute("$search", "$engine");
[download]

search and engine are irrelevant right now, it's just time that's not coming back I'm assuming. The time column is of datetime and this is all I used to set it all up.

    "CREATE TABLE IF NOT EXISTS searches
    (
       id int auto_increment NOT NULL,
       search VARCHAR(150) NOT NULL,
       engine VARCHAR(20) NOT NULL,
       time datetime,
       primary key  (id)
    )");
[download]

Thank you.

my $data = qq(SELECT search, engine, time FROM searches WHERE DATE_SUB
+(CURDATE(),INTERVAL 7 DAY) <= time);

my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

while(my $r = $sth->fetchrow_hashref)
{
  print "$r->{search} $r->{engine} on $r->{time}<br>";
}
[download]

Maybe I'm being dense, but I don't see how this solves the OP's problem. The bind_columns/fetch code looks right to me. I think it's probably an SQL or data problem.

It may not solve Anonymous Monk's problem, but it does remove a specific issue: fetch is not a documented method of statement handles, so its behavior is effectively undefined. Switching to a documented method such as fetchrow_hashref should result in predictable behavior from the statement handle, which lets us focus on whatever may be causing the behavior. And if changing to the defined method results in the problem going away, well that's just groovy.

Update: Okay, so it is documented after all. Took me a while to scan through DBI, but fetch is an alias for fetchrow_arrayref with the apparent difference that:

If any method except fetch fails, and "RaiseError" is not set, selectcol_arrayref will return undef. If fetch fails and "RaiseError" is not set, then it will return with whatever data it has fetched thus far. $DBI::err should be checked to catch that.

So it's there in the docs after all, but it's not nearly as well defined as the others. That's what I get for just scanning through the headers of the POD rather than looking at the examples. Oy.

Yeah, I'm an idiot. Sorry about the response. Should have picked up the err in the SQL.