PHP MYSQL Insert/Update

Question

I have a simple table as below.

CREATE TABLE `stats` (
  `id` int(11) NOT NULL auto_increment,
  `zones` varchar(100) default NULL,
  `date` date default NULL,
  `hits` int(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

So just storing simple hits counter per zone per day.

But I just want to increment the hits value for the same day.

I have tried the MYSQL DUPLICATE KEY UPDATE but this wont work as I may have many zones on different dates so I cant make them unique or dates.

So the only way I can think is first to do a query to see if a date exists then do a simple if() for insert/update

Is their a better way of doing such a task as there maybe be many 1000's hits per day.

Hope this makes sense :-).

And thanks if you can advise.

Answer 1

Declare the tuple (zone, date) as unique in your CREATE statement. This will make INSERT ... ON DUPLICATE UPDATE work as expected:

CREATE TABLE `stats` (
  `id` int(11) NOT NULL auto_increment,
  `zone` varchar(100) default NULL,
  `date` date default NULL,
  `hits` int(100) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE (`zone`, `date`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO stats (zone, date, hits) values ('zone1', 'date1', 1) ON DUPLICATE KEY UPDATE hits = hits + 1;

Answer 2

$result = mysql_query("SELECT id FROM stats WHERE zone=$zone AND date=$today LIMIT 1");
if(mysql_num_rows($result)) {
    $id = mysql_result($result,0);
    mysql_query("UPDATE stats SET hits=hits+1 WHERE id=$id");
} else {
    mysql_query("INSERT INTO stats (zone, date, hits) VALUES ($zone, $today, 1)");
}

Something like that, if I've interpreted you correctly... that's completely untested. You can figure out what the variables are.