Insert / Update PHP MYSQL

Question

I have a simple table as shown 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 keep a simple hit counter for each zone per day.

But I just want to increase the value of hits on the same day.

I tried MYSQL DUPLICATE KEY UPDATE, but this does not work, because I can have many zones on different dates, so I can not make them unique or dates.

So, the only way I can think of is to first make a request to see if the date exists, and then make a simple if () to insert / update

This is the best way to do such a task, as there can be many 1000 hits per day.

Hope this makes sense :-).

And thanks if you can advise.

Answer 1

(zone, date) CREATE. INSERT... ON DUPLICATE UPDATE :

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)");
}

- , ... . , .