MySQL 4 - Count of records within the past 7 days not correct

Hello all,

I have a query where I would liek to pull the count of all the records added to various tables within the last seven days. For some reason the results are incorrect and I can't seem to figure out why. All the tables listed int he query below have not had any records added since 1/19/2008 (10 days ago) but the query says there is one for the members table and one for the brewery table. Can anyone tell me what I am doing wrong?


$query_weekly_stats = "
SELECT COUNT( * ) AS `stats`
FROM `members`
WHERE WEEK( `CreateDate`, 7 ) = WEEK( NOW(), 7 )
UNION ALL
SELECT COUNT( * ) AS `stats`
FROM `brewerytable`
WHERE WEEK( `BreweryCreateDate`, 7 ) = WEEK( NOW(), 7 )
UNION ALL
SELECT COUNT( * ) AS `stats`
FROM `beerstable`
WHERE WEEK( `BeerCreateDate`, 7 ) = WEEK( NOW(), 7 )
UNION ALL
SELECT COUNT( * ) AS `stats`
FROM `labels`
WHERE WEEK( `create_date`, 7 ) = WEEK( NOW(), 7 )
UNION ALL
SELECT COUNT( * ) AS `stats`
FROM `bottlecaps`
WHERE WEEK( `create_date`, 7 ) = WEEK( NOW(), 7 )
UNION ALL
SELECT COUNT( * ) AS `stats`
FROM `coasters`
WHERE WEEK( `coaster_create_date`, 7 ) = WEEK( NOW(), 7 )
";
$weekly_stats_result = mysql_query($query_weekly_stats);
while ( $stats_data = mysql_fetch_assoc( $weekly_stats_result ) ) {
$weekly_stats .= $stats_data['stats']."<br />";

have you broken out the ones that are returning the incorrect data to see the record and the dates in it that is returning?

I am having a problem because in my development environment it works perfectly fine. I didn't want to mess with production. I guess I will have to.

Thanks,

Stephen

I was finally able to see what records are being included that shouldn't be. Today when I went to the site it said that there were 6 new labels and 5 new bottlecaps. When I looked at the database I found that it has something to do with how the date is being read.

The query is pulling items with the following date

2007-04-07 14:21:46
2007-04-07 15:57:21
2007-04-07-15:57:57

Not
2007-12-12 06:21:23
2007-12-27 17:25:12

So it looks like it is pulling items regardless of the year AND up to seven days in the future. I would like to only have items that were added within the LAST seven days.

Any help would be greatly appreciated.

Stephen