[SOLVED] Code for making selection based on date?

[R0CKY]

I have a database filled with records, each one of which has a file_time field that is stored in a format like this "1101765599" - I assume that's some kind of standard time code that can be changed into a standard date.

 

What I'd like to do is have php count how many records were added in the last 7 days. Each file has a sequntial File_ID field so that could help...?

 

So to break it down, I think this would be the steps.

 

Assign the most recent File_ID to variable A

Assign the current date to variable B

Subtract seven days and assign that date to variable C

Find the first record with File_time matching variable C

Subtract the File_ID of that record from variable A - and that would be the count.

 

I think that's the best logic, but I would need pointers with the php.... anyone start me off please?

 

 

[paul2463]

the number you mentioned is a <i>timestamp</i> and equates to 29th November 2004 if you want to know all entries in the last 7 days you need the <i>timestamp</i> for 7 days ago

<?php
$today = strtotime("NOW"); // timestamp for today
$lastweek = strtotime("-7 day", $today); //take off seven days
$query = "SELECT * WHERE File_time > '$lastweek'";
?>

[R0CKY]

Well, that'll be that then!

 

Thanks you very much for the detailed reply, much appreciated.

[Barand]

If you want to know how many were added

<?php

$sql = "SELECT COUNT(*) FROM tablename
         WHERE FROM_UNIXTIME(file_time) > NOW() - INTERVAL 7 DAY";
$res = mysql_query($sql);
$recsAdded = mysql_result ($res, 0);

[R0CKY]

Thank you both, I pasted in Barand's code and it worked first time.

 

You can see it in action at http://www.ghostrecon.net/, scroll down, right hand side "Latest Mods" boxout.

 

Many thanks!