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.

Ayman Hourieh · Accepted Answer · 2009-05-09 23:07:48Z

7

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;

answered May 9, 2009 at 23:07

Ayman Hourieh

138k23 gold badges149 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lee Over a year ago

I did say I tried to use the DUPLICATE KEY UPDATE and it wont work because zone & date can't be unique. Example a zone will have multiple days ie. zone - date - hits zone1 - 2009-05-11 - 100 zone2 - 2009-05-11 - 55 zone1 - 2009-05-10 - 120 zone2 - 2009-05-10 - 88 See ig I hade a uniqe date or zone I would only have 2 records from the above example. :-(

Ayman Hourieh Over a year ago

UNIQUE(zone, date) ensures that zone and date are unique together. It is perfectly fine to have multiple entries with the same zone as long as dates are different. The same applies to multiple entries with the same date and different zones. But there must not be two entries with the same zone and date. I think this matches your requirements. Give it a try in a test database and see if it works.

Lee Over a year ago

Ill try again Ayman. I had excatly what you had above but ill try again now.

Lee Over a year ago

OK I know what was the problem I had. I hade 2 unique names for each unique field ie. KEY zone (zone), KEY date (date) So working perfect. I was close but Thank you.

mpen · Accepted Answer · 2009-05-09 22:41:17Z

1

$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.

answered May 9, 2009 at 22:41

mpen

285k289 gold badges896 silver badges1.3k bronze badges

2 Comments

Martin Spamer Over a year ago

This solution suffers from a potential race condition.

mpen Over a year ago

Then use the better solution above :p Didn't think about that. Good call ;)

Collectives™ on Stack Overflow

PHP MYSQL Insert/Update

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related