Re: sth fetch only grabbing back first result

I would handle this in a slightly different manner. I don't use bind_columns() only because that's not the way I was taught. Therefore, this method works for me when I am trying to figure out what data is where and how its being fetch'd.

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

Comment on Re: sth fetch only grabbing back first result Select or Download Code

Replies are listed 'Best First'.
Re^2: sth fetch only grabbing back first result by Anonymous Monk on Aug 10, 2006 at 19:20 UTC
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().	[reply]
Re^3: sth fetch only grabbing back first result by madbombX (Hermit) on Aug 10, 2006 at 19:39 UTC
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	[reply] [d/l]
Re^3: sth fetch only grabbing back first result by Ieronim (Friar) on Aug 10, 2006 at 19:43 UTC
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`	[reply] [d/l] [select]