5

Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I go about having a new _views row for instance created upon the first visit to ID: 1106 or 1107 with a MySQL insert?

ID          key         value
-----       -----       ------
1104        _reserve    10000
1104        _views      100
1104        _watchers   2
1104        _inquiries  1
1105        _reserve    1500
1106        _reserve    24000
1106        _views      236
1107        _reserve    45300
5
INSERT INTO mytable (ID,`key`,`value`) VALUES (1106,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

and

INSERT INTO mytable (ID,`key`,`value`) VALUES (1107,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

If your table has value defined as

CREATE TABLE mytable
(
   ...
   value INT DEFAULT 1,
   ...
);

then the INSERTs would be

INSERT INTO mytable (ID,`key`) VALUES (1106,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

and

INSERT INTO mytable (ID,`key`) VALUES (1107,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;
  • I'll test it out and let you know. – Jerry Tunin Mar 5 '14 at 18:37
  • Doesn't quite work as I don't believe there is a unique key. – Jerry Tunin Mar 6 '14 at 18:36
  • The unique key needs to be (ID,key) for this to work. Sorry, I should have said that earlier. – RolandoMySQLDBA Mar 6 '14 at 18:37
  • @RolandoMySQLDBA Is this better then a select by keys and then running an additional query? – Alex Pelletier May 22 '18 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.