How to express a date range - the previous 7 days

[Anon]

I would like to ask mySQL for records from the previous 7 days - not including today (that's important). That way I can have a complete set of records for each of the 7 days.

Here is what I have so far: ((to_days(now()) - to_days(formDate)) <= 7)

I can seem to find something to express today minus one day.

FH

[Anon]

It just so happens I was finishing something that does just what you ask. PROVIDED you use a timestamp in mySQL. This is working in PHP3:

function row_color($cnt,$even,$odd) {
print ($cnt%2) ? "<tr bg color=\"$odd\">" : "<tr bgcolor=\"$even\">";
}

echo "<hr><blockquote>\n<table cellspacing='2' cellpadding='2' width='90%'>\n";
echo "<tr>\n<th align='left'>Added or modified in the last 7 days(before today):</th>\n<th align='left'>Date added/modified:</th>\n</tr>\n";

mysql_connect("$DBHost","$DBUser","$DBPass") or die ("<br><b>The database server is temporarily down for maintenance...</b>");
$result=mysql("$DBName","SELECT address, urlID, entry_time FROM recURL WHERE TO_DAYS(NOW()) - TO_DAYS(entry_time) < 8");
$cnt=0;
while ($Nrow = mysql_fetch_row($result)) {
$Nurl=$Nrow[0];
$Nnum=$Nrow[1];
$Ntim=$Nrow[2];

$pieces=explode(":",$Ntim);
if ( ereg( "([0-9]{4})([0-9]{1,2})([0-9]{1,2})([0-9]{1,2})([0-9]{1,2})([0-9]{1,2})", $Ntim, $regs ) )
if ($Nnum != "") {
row_color($cnt++,"#F8F8F0","#FFFFFF");
echo "\n<td valign='top'>\n";
setlocale ("LC_TIME", "C");
$today = (strftime("%d"));
if ($regs[3] != $today)
echo "<img src=\"../images/ico_new.gif\"> <a href=\"$Nurl\" target=\"_blank\"><b>$Nurl</b></a></td>\n<td valign='top'>$regs[2]/$regs[3]/$regs[1] $regs[4]:$regs[5]:$regs[6]</td>\n</tr>";
}
}
echo "\n</table>\n";

By way of explanation, what this does is store the php function, strftime()in the var $today which is compared to the third regex from the timestamp explosion, then conditionally print the row results from the query in alternating row colors.

[Anon]

Hi
your MySQL didn't work for me (I have a 'date' feild)

so after delving further I came up with this

$sql = "SELECT * from table where DATE_ADD(my_date_feild, INTERVAL $backdate day) >= DATE_FORMAT(now(),'%Y-%m-%d')";

where:
my_date_feild = the date feild in my table
$backdate = the number of days we're interested in (in this case 7)

hth (someone)

Bealers

[Anon]

Hey!

Good solution if you have a 'date' field -- I have snipped this and added it to my toolbox. Thanks! Nice PHP/mySQL combo!

[Anon]

this also works for 'date'
sorry, but i had to check =)

select created from vacancies WHERE (TO_DAYS(NOW()) - TO_DAYS(DATE_FORMAT(created,'%Y-%m-%d'))) < 8;

Bealers

[Anon]

I continue to get today's entries with this code - seems like it should work though. I'm pretty baffled.

$backdate = 7;

$sql = "select * from table where DATE_ADD(formDate, INTERVAL $backdate day) > DATE_FORMAT(now(),'%Y-%m-%d')";