Compare current date to timestamp to return results from last 40 days

[kjdix]

Hey,
As you can probably already tell im very new to php.
Im editing a current report that displays a list of all data of a certain type from a table, see below.
$cxn = mysql_connect($dbhostname,$dbusername,$dbpassword)
or die ('Unable to connect!');

//select database
mysql_select_db('itsupport') or die ('Unable to select database!');

$result = mysql_query( "SELECT * FROM incidents where type like 'A computer'")
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
print "<table width=200 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td><font face=arial size=1/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
}
mysql_close($cxn);

What i need it to display is only the results from the last 40 days. There is a timestamp field and time logged field that i can query. Ive been looking at this and for some reason cant get my head around how i should format and the best way. Thankyou.

[coldwerturkey]

please use [php][/code] tags when displaying php

your time stamps, are they unix time stamps or YYYY-MM-DD HH:MM:SS timestamps?

if its unix timestamp you can do:

$query = "SELECT * FROM incidents WHERE type LIKE 'A computer' AND WHERE thetime >= ".(time() - 60*60*24*40);
$result = mysql_query($query) or die("SELECT Error: " . mysql_error());

if its the ladder, someone more knowledgeable then me can help you out further.

additionally, your displaying method of showing info from the database is rather... questionable... you're using foreach to show each value of the array, then closing the tables row in the loop.. then closing the table on the while loop.. how about trying something like

print "There are $num_rows records.<table width=200 border=1>\n";
while ($get_info = mysql_fetch_row($result)){ 
	print "<tr>\n";
	foreach ($get_info as $field) 
		print "\t<td><font face=arial size=1/>$field</font></td>\n";
	}
	print "</tr>\n";
}
print "</table>\n";


//or, if you wanted better control of how each cell will be displayed..


print "There are $num_rows records.<table width=200 border=1>\n";
while ($get_info = mysql_fetch_row($result)){ 
	print "<tr>\n";
	print "\t<td><font face=arial size=1/>{$get_info['column1']}</font></td>\n";
	print "\t<td><font face=arial size=1/>{$get_info['column2']}</font></td>\n";
	//etc...
	print "</tr>\n";
}
print "</table>\n";
//(Where column1 & column2 are the columns you wish to display)

[kjdix]

At the moment it is still not specifying the timeframe within the last 40 days.
So the timestamp is gathered from this when a incident is reported

$time=date("Y-m-d H:i:s");

which adds it to the database like so
2009-01-30 10:48:42

I then want to query this information and return all the results logged within the last 40 days.
My code is as below.

$query = "SELECT * FROM incidents WHERE type LIKE 'A computer' AND timelogged >= ".(time() - 60*60*24*40); 
$result = mysql_query($query) or die("SELECT Error: " . mysql_error()); 

$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<table width=200 border=1>\n"; 
while ($get_info = mysql_fetch_row($result)){ 
    print "<tr>\n"; 
    foreach ($get_info as $field) 
        print "\t<td><font face=arial size=1/>$field</font></td>\n"; 
    } 
    print "</tr>\n";  

print "</table>\n";

Any help would be much appreciated. Thankyou

[Installer]

Try this:

$query = "SELECT * FROM incidents 
          WHERE type LIKE 'A computer' AND DATEDIFF(CURDATE(), timelogged) <= 40";

See MySQL date and time functions.