My biggest challenge to date-weekly/monthly/total stat tracking. Need a PHP Wiz here.

Dolemite50

Hi, I have a site with songs. The weekly/monthly/ and total stats are tracked for plays, score and downloads. My current method is terrible. To make a long story short; it tracks each play by putting a row into the monthly_plays table with songid and date. A nightly CRON deletes all entries > 30 day and then tallies the rest to find the total plays for each songid. It then runs through again and tallies < 8 day for weekly total. A server nightmare.

I'm thinking of one table with the (unique) songid and fields for each stat
stats_daily
songid - PRI KEY
plays
downloads
score

When a song is played it will "update plays=plays+1 where songid=songid" instead of adding a row. Every night a CRON loops through the table, deleted entries < 8 day, and inserts all rows from the daily_table into the weekly_table along with the date.
stats_weekly
songid
plays
downloads
score
date

So now each songid should have 7 entries in the weekly table and I can just use the SUM of "plays" column for the weekly plays.

Here are some concerns already:
Should I truncate the Daily Table or just set all stat fields to 0? It would be nice to have each songid already existing so I wouldn't have to check to see if it exists before updating it. I considered REPLACE INTO but that wouldn't increment the plays value. Maybe do a speedy truncate and then loop through the songs table to populate with songids again?
Can anybody think of a method that would allow me to store the weekly_plays total as one row instead of the 7 rows I'm considering? I've thought about having a table strictly for plays with 15 rows.
weekly_plays
songid
plays1
playsdate1
plays2
playsdate2
...
plays7date

It would then update the oldest playsdate and matching plays column; therefore only having one row per songid and the benefit to keep songid as a unique field. I don't know how I could tell php to "find the oldest playdate column, (playdate6) and then update playdate6 and plays6"?

I would also need to duplicate whatever method I use for the monthly_plays so then we'd be talking about 60 plus columns so that may not be the way to go either.

Any suggestions? I've been struggling with this one for a year.

trexx

it's me again :-)

well... i've monthly and overall stats on my page too for pages, images, articles, etc. what i've done there is that:

PAGE
--------------------
PG_ID
PG_TITLE
PG_VIEWS
PG_VIEWS_MONTHLY
PG_VIEWS_WEEKLY

when someone accesses the page i do

update page set 
pg_views = pg_views +1, 
pg_views_monthly = pg_views_monthly +1, 
pg_views_weekly = pg_views_weekly +1 
where pg_id=$x

every week i do "update page set pg_views_weekly=0"
every month i do "update page set pg_views_monthly=0"
i never reset the overall views

Dolemite50

Thanks once agan.
I wound up doing something similar. I gave up on trying to have it be more dynamic by tallying every day so that "this month" was always the last 30 days; 7 for week.
I think my approach may be slightly overkill but it sure blows away the old one. I have about 5 columns that I am tracking daily, monthly, and weekly stats on. I just put them all in one table like
STATS
song_id
day_plays
day_downloads
day_score
day_tofaves
day_toemail
week_plays
week_downloads
month_plays
month
....
I set up another reference table called daycounter that has only two fields, daycount, weekcount, TINY INT
The midnight cron empties the day columns and adds them to the corresponding week columns, updates the daily stats in song_table, then sets daycount=daycount + 1
The crons first task is to see if daycount >=7, if so, it empties the day_column, adds them to the week column, updates weekly stats in song_table; then empties the week_culumns and adds them to the month_columns, sets daycount back to 1, and adds 1 to weekcount. It will repeat until daycount is 7 and weekcount is 4, where it updates daily stats, adds day to week, updates weekly stats, adds week to month_, updates monthly stats, and then empties the entire table and sets day and week counters back to 1.
This will generally be showing the previous day/week/month's activity; which I chose over somebody showing up near the first of the month and only seeing a few plays in their monthly total.

hmm,..it's looking pretty laughable compared to yours. 😃
The only possible advantage I can see here is that I'm only updating one table every time a fields is incremented, but adding 1 aint nothin' but a meatball. Or is it? With all stats we're likely talking 100k daily. The cron job is really fast because it mainly just adds two columns, so should I carry on?

I'm indebted to you, thanks again.