Keeping statistics....

[eluser]codelearn[/eluser]
I have a fun/interesting problem for anyone willing to tackle it. I'm not looking for code or anything, just an idea of a spec for this functionality.

My website has a bunch of restaurants/business' which list their info. We also have created a backend for these places so that they can edit all of the info. Now, they are asking me to create some sort of bar graph of how many people access their specific listing each month.

The exact problem is how to collect the hits (CRON job, on page load?), how to store them (a field for each month? but then what about next year? we would like an archive) and then finally how to display them in a graph (least important).

This task isn't horribly daunting, but If anyone has done something similar I would greatly appreciate your input that would save me a great deal of time.

Thanks all.

[eluser]Michael Wales[/eluser]
Create a table:
date
hits

On each page load:
1. Check if a row for today exists, if not create it
2. Check for unique IP if you want visitors (otherwise you are counting hits)
3. "UPDATE stats set hits = hits + 1"

For graphs: Google Chart API

[eluser]codelearn[/eluser]
Thanks Michael,

Is that going to scale well with over 300 restaurants?

[eluser]Michael Wales[/eluser]
Yeah, just add in an ID field to the table to reference the restaurant and change step 1:

1. Check if a row for today exists for the current restaurant, if not create it

[eluser]codelearn[/eluser]
Quick question:

Is there a way to write that update query with Active Record... something like:

$this->db->where('rest_id',$IRIresults->id);
$this->db->where('date', $todays_date);
$this->db->set('hits','hits+1'); <--**This does not work, obviously**
$this->db->update('rest_stats');

[eluser]Nick Husher[/eluser]
Just as general question based off of this, but would it be worthwhile to create a set of hooks to do this to keep the statistics flexible and aspect-oriented?

[eluser]codelearn[/eluser]
Nick,

That might make sense... let me know if your on a similar project and do something like that. I will do the same.

Update:

For anyone who cares, this is how it was implemented (Following Michaels suggestion):

Database Table:
---------------

Code:

CREATE TABLE `rest_stats` (
  `id` int(12) NOT NULL auto_increment,
  `rest_id` int(12) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `hits` int(125) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

Controller Code:

Code:

//compute stats
$todays_date = date("Y-m-d");
$this->db->where('rest_id',$IRIresults->id); //which restaurant
$this->db->where('date',$todays_date);
$rest_stats = $this->db->get('rest_stats');
      if($rest_stats->num_rows() > 0){
                    $this->db->query('UPDATE rest_stats set hits = hits + 1 WHERE rest_id = "'.$IRIresults->id.'" AND date = "'.$todays_date.'"');
                } else {
                    $this->db->set('hits','1');
                    $this->db->set('rest_id',$IRIresults->id);
                    $this->db->set('date',$todays_date);
                    $this->db->insert('rest_stats');
                }
//end