7 day statistics.

[FnTm]

Hi! What I need to do, is create a statistic table for news items.

So I have this MYSQL table, that has the startingdate
(starttime) for my news and ending time (endtime). Upon the first inserting, only the `starttime` is entered, and the `enditme` is left blank. Once I clik a button on my webpage, the endtime is inserted, and the news item dissapears from my site. But what i would like to make, is this statistic table, where I can see a 7day overview that is sorted by categories. Categories beeing rows, and days beeing columns.

But the thing I cant figure out is, is the query to get this.
I have set up a for loop with 7 days, and each time the loop executes, it enters into mysql query, that has DATE_SUB(CURDATE,INTERVAL $i day) so, that I get the date that i nead, for each loop execution.

What I cant understand, is how do I check, if the date is

DATE(starttime)>=DATE_SUB(CURDATE,INTERVAL $i day)<=DATE(endttime)

The thing that happens now is, that it outputs even those DB rows, that are older than DATE(starttime). How do I fix this?

Thanks FnTm!

[jatar_k]

"where I can see a 7day overview that is sorted by categories"

overview of what? you mention statistics but not the data you are trying to show

active items per day? (wild guess)

[whoisgregg]

One thing that jumps out is that you can't put three values and two logic operators into the same statement:

a >= b <= c

That just doesn't work, you have to do each separately:

a >= b AND b <= c

So, for your code, you'd want to rewrite the date range code more like so:

DATE(starttime) >= DATE_SUB(CURDATE,INTERVAL $i day) AND DATE_SUB(CURDATE,INTERVAL $i day) <= DATE(endttime)