[SOLVED] Limiting table output to recent x number of days

[StanLytle]

No matter what I try, I can't get the table that is output to be limited by the cut off time (ie: last 10 days).  I finally got rid of all the mySQL errors, but now I'm stumped.  Any suggestions appreciated.

 

$CutOffTime = strtotime('-14400 minutes', $CurrentTime);

$query = "SELECT UserID, Name, COUNT(UserID) FROM Rejections WHERE (Date > '$CutOffTime') GROUP BY UserID ORDER BY UserID";

$result = mysql_query($query) or die(mysql_error());

while(list($id, $name, $ct) = mysql_fetch_row($result))

{

echo "<tr>";

echo "<td>$id</td>";

echo "<td>$name</td>";

echo "<td>$ct</td>";

  echo "</tr>";

}

[rhodesa]

what field type is is the Date column?

[StanLytle]

The date column is int(20).

[rhodesa]

um, the only other thing i see wrong is i think the GROUP BY needs the name in it to:

$query = "SELECT UserID, Name, COUNT(UserID) FROM Rejections WHERE (Date > '$CutOffTime') GROUP BY UserID, Name ORDER BY UserID";

 

is it outputting anything at all? what is it showing and what do you expect? if you echo the value of $query just before executing it, what does it say?

[StanLytle]

The table outputs, the structure is fine, it counts and groups correctly, but it will not output only the most recent days as set by $CutOffTime.  It outputs the entire table, from day 1.  I want to limit the output to only whatever number of days that I specify.

 

Stan

 

[rhodesa]

before the query, if you echo the values of $CurrentTime and $CutOffTime, what are they?

[StanLytle]

I think that you are onto something.  $CutOffTime is -864000, but $CurrentTime is blank (no numbers, not even 0).  So $CurrentTime must not have any vlaue.  How do I get the current time into $CurrentTime?

[rhodesa]

if all you care about it relative to now, just drop that variable all together:

$CutOffTime = strtotime('-14400 minutes');

 

p.s. - you should be able to do days instead of minutes too:

$CutOffTime = strtotime('-10 days');

[kickstart]

Hi

 

Can't you just leave off $CurrentTime from strtotime?

 

All the best

 

Keith

[StanLytle]

I went with $CutOffTime = strtotime('-10 days'), and it worked just as I wanted it to.  Thank you for all of your help.

 

Stan