next »

[svakanda]

This is for a PPC / Contextual advertising system I use to host all my activities.  It serves up landing pages to the 'users' from my ad networks, and dynamically manages all my shit.  It isn't pretty or particularly nice, but it does what I need it to do...I have decided to rework it so as to potentially make it useful to other persons besides myself in the future.

I am adding some more stats/reporting to this system.  Before...I used to log a 'hit' as a single record in a table, then had a process to count / sort them whenever they needed to be displayed.  As you can imagine, after a few months of data, trying to display this would quickly haul my server down into the dust.  I am going to try and plan out my database a bit more then the last version...and try to fashion something that would be thorough, fast, and scalable.

So i am thinking, obviously, I need something that is going to count as I go and update the fields, so when I want display the stats, the server only has to do 2-3 database calls instead of like 50k on top of heavy php processing.

But if I want to keep data on particular days too...I feel like that doesn't quite satisfy.  Perhaps then I make records for each day...but then my ITEMS that are being reported on may constantly change....and I cannot modify the number of fields in an entry on the fly.  So I suppose I need to create a crosslinked set of tables for items & days.  I'm sure plenty of other people have arrived at this juncture where you discover your database design was far less then perfect, any helpful suggestions or theories or reading on the subject that anyone could share would be great.

It also occurred to me to use a combination of the two ideas...and keep a table of the raw 'hit' information, but then also build/maintain tables of the calculated information, which the app would calculate as it goes, and then rely on for the majority of the reporting.  I think it will be getting a total of around 1000 hits per day in this situation, but I would like everything to be relatively scalable since I am going to put in the time to upgrade my system anyhow.

I'd love some input.  Thanks guys!

[perkiset]

This is a common problem.

I distill daily stats into a single table record. It's a cron job that takes everything from today (distinct surfers, page hits, average page duration, overall duration, purchases, abandons etc) and totals/averages them into one record, so that I wind up with 365 rows per year. After the distillation, the data is removed.

In cases where I want to watch stuff, the delete portion of the distiller is set back to something like 30 days ... so that I can look back and see what happened with more detail. Particularly when I am debugging the usability of a site, I like to see every single click in sequence - I can often tell if my site is confusing users or they are "getting it" by looking at this data. But after a couple months, this data is utterly useless.

It is then easy to do monthly reporting, week over week, year over year etc on a small distilled table that is plenty for my historical purposes.

[svakanda]

Well, it is good to hear that I'm not the only one!  So...here is what I am looking at now.  You see, I have like anywhere from 20-50 'campaigns' running at any time.  And I want to save hit information on a campaign level.  Do you think that distilling the stats just as you said, only giving each record another field that contains the campaign id it is pertinent to, is a feasible solution?  That way I can just query the table for this day & this campaign id....which means also I'd be doing like 20-50 records per day instead of 1.

If I were to do this...I could tell mysql to index the records by campaign id & day as well to help with performance correct?  There are PRIMARY, UNIQUE, and INDEX indexes.  Which of these would be appropriate to use in that situation?  The vanilla id of the records themselves would be PRIMARY of course...so I am guessing just plain old INDEX?  Or is it even necessary for the scope within which I am working?

Thanks Perk!  I really appreciate the input.

[perkiset]

Oh absolutely ... I don't have 1 record per day in my table... I have 1 record per day in my table per application LOL ... I have lots of ecords per day mate

I use a numeric site_id field that is included with the distilled data so that I can either break out by application, or groups of applications (site_id=1 or site_id=2) or to see what the total load is on (that box). I'm still not even putting in excess of 20K records in a table per year per box ... so this is really trivial amounts of data.

[svakanda]

Awesome.  I'm setting it up right now.  Going to use your DB class too.  It's the first time I've ever used a DB tool other then plain php mysql functions.  Figured I ought to play with it in hopes of making things easier on myself.  Thanks for all the help!

[kurdt]

Yeah, Perk is right. I had the same problem with one of my products. I solved it just like Perk but I always thought about it like cache database.. like where you generate and cache all the common counts and reports you use.

[svakanda]

So Perk, the connected() function returns a 1...however when I try and run the query function I receive this....

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/../admin/includes/dbConnection.php on line 97

I am pretty sure it is referring to the $__myConnection class variable, but I cannot figure out why.

Code:

include("dbConnection.php");                                                                         
$db = new dbConnection(MUNGED BY PERK - Careful Sva, don't want that stuff our here,true);
 

   

It is being called from inside another class...

Code:

function dbsubmit() {
Global $db;
$sql = 'INSERT INTO targets (name,adpointer,cookiepointer,statpointer) VALUES($__name,$__adpointer,$cookiepointer,$statpointer);';
$db->query($sql);
}

Any ideas would be helpful, I will keep trying.

[svakanda]

got it.  apparently my SQL memory just sucks...a more correct statement is

$sql = "INSERT INTO targets (name,adpointer,cookiepointer,statpointer) VALUES('$tname','$tadpointer','$tcookiepointer','$tstatpointer');";

[svakanda]

er however...i am still getting errors from your class, I just reverted back to a normal connect.

[perkiset]

Don't worry about connected.

First off, just do this:

$db = new dbConnection(parm, parm, parm, parm);
$db->query("insert into targets(field, field, field) values('$value', '$value', '$value')");

Note that you must still put your string values within quotes Sva... when you say "values($__name ..." you will always get a SQL error because you have not enclosed your string value.

The only time you don't enclose variables in quotes is if you are writing a stored procedure or function in the DB itself.

(oops, just saw that you corrected that)

Then do this:

$db = new dbConnection(parm, parm, parm, parm);
$db->query("insert into targets(field, field, field) values('$value', '$value', '$value')");
echo "{$db->lastQuery()}\n{$db->lastError}\n";

... and you'll see exactly what you TRIED to send to the db connection and exactly what the error was.

[svakanda]

I will try that out Perk, thanks! 


getting kind of tired.  I think I got all the preliminary classes done though.  It looks like the whole program is going to be quite a bit simpler when written in OO php5 + sql as opposed to large collections of simple functions and flatfiles.   =)  so that is good news.

[kurdt]

$db = new dbConnection(parm, parm, parm, parm);

What the hell is 'parm'? What happened to 'foo' and 'bar'? Goddamn newage hippies!

[perkiset]