Scheduled database maintenance?

dustbuster

Hello all,

I need some way to do the following:

I have a MySQL database with a table that tracks monthly statistics. There are only 2 columns per month. I need some way to ensure that the next month's totals are set to zero (to ensure accurate statistics).

I need a reliable way for this maintenance to be run a few days (or the day before) the next month begins but I don't want to have to babysit it.

Any help would be greatly appreciated.
Thanks

[deleted]

"I need some way to ensure that the next month's totals are set to zero "

Why don't you just add a month+year column and log the stats for the current month/year value and forget all about resetting the stats.

dustbuster

I would like to be able to see the stats for the last 11 months or so. This way I can compare stats for a longer period of time.

jweissig

hi,

can you show us the database design and some exmaple data if you only have 2 fields it should be easy..

Justin

dustbuster

The data is for my own banner tracking system.
It's for my own small site.

It looks like this:

1Shown | 1Clicked | 2Shown | 2Clicked |.....| 12Shown | 12Clicked
20__| 10 | 50| 20 |.....| 10 | 2__

Each row represents a banner
Each value is the stats for the banner
Each number represents the month
1Shown=January
2Shown=February
.
.
.
12Shown=December

Now say we are in month 1 (January 27) for example
I want to reset month 2 to zero. That way when I get to month 2 and start incrementing the data it will be starting from 0

Thanks
D.

[deleted]

This is not a good database model.

I suggest you change the model so that you have only one set of columns and add a new record for every month and update that record.

That way you can log indefinately and you don't have to reset anything.

mikeatrpi

Google for CRON. You should be able to set up a cron job (unix; or Windows Scheduler on Windows) to run a PHP script from the command line every month. Then code that PHP script to do your work.

jweissig

hi,

Just a couple suggestions on how to redesign your database. You could create 2 tables the first one would manage all your banners and the second could hold the click and a timestamp for each banner id.

Here is just an example (these will not compile because some syntax is incorrect)

banners {
pk_id int not null autoincrement,
name var(50) not null,
active bit(1) default "0",
timestamp datetime
};

banner_clicks {
pk_id int not null autoincrement,
fk_banner_id int not null,
timestamp datetime
};

Using this example you can insert banners into the banners table and then using your ad tracking software insert a banner impression into the banner_clicks based on the banner.pk_id

This will allow the tables to grow and you will not have to reset anything anytime. This will also allow you to select an entire month or months based on the number of clicks for each banner per month.

Or you can check out phpadsnew at http://www.phpadsnew.com/one/ it has awesome support and it activly maintained but might be a little overkill 🙂

Thanks
- Justin

dustbuster

Thanks for the replies

vincente:
A further comment on how this is "not a good database model" would be appreciated.

Can you clarify your suggestion?

As I understand your suggestion the table would be structured like this:

Banners

BannerID | January_2003_Shown | January_2003_Clicked

Then add 2 new columns each new month.

Will this make my table significantly larger as I add more banners?

mikeatrpi:
Thanks for the CRON suggestion. I think it is what I was looking for and I will look into it further.

Thanks again.

[deleted]

"Then add 2 new columns each new month.

Will this make my table significantly larger as I add more banners?
"

No, My suggestion is:

bannerid | year | month| count/whatever you want to record

Every record contains the year and month for which you want to record the data. Whever you update the data you update the record that has the current year and month. If no such record exists, you create one. (check the mysql_affected_rows() from the update, if no record exists it will return zero)

This way you get data like:

1 | 2003 | 1 | 456
2 | 2003 | 1 | 125
1 | 2003 | 2 | 45

and so on. Now you can log indefinately and you never have to reset anything.

Don't worry about getting lots of records, mysql is not impressed by a few million records.

dustbuster

Thanks vincente

I get it now and it sounds like just what I need. I think I will do it that way.

Thanks again for your help.
D.

[deleted]

Good luck, let me know if you have any problems!