MySQL - Increment Column Value or Insert Data if not Exists

Question

I have users visiting the site. The user can do a bunch of different actions. I'd like a counter to count the amount of times the user does that action. The problem is, it's per day, it starts over each day. So the model has, Id, User, Action, Times, Date

I'd like to use this, but I can't because Action is not a key, and cannot be. None of the other fields can be a key either.

insert into useractions (user, action, times) values (2, 3, 1)
on duplicate key update times = times + 1;

Answer 1

You left the data column out of your insert example, but you mentioned it several times so I'll assume that exists. Also, I'm assuming it's an actual date (not a timestamp or datetime).

If you add a unique index on (user,action,date) then your query will work.

Here's the DDL:

alter table useractions
  add unique index unique_idx (user,action,date);

And your DML (adding the date column):

insert into useractions (user, action, times, date) values (2, 3, 1, current_date())
on duplicate key update times = times + 1;

Answer 2

Do you absolutely want to calculate the counter value at the time of inserting the action? It may be simpler to simply keep track of the users and actions with timestamps, like so:

+--------+----------+---------------------+
| UserID | ActionID | Time                |
+--------+----------+---------------------+
|      1 |        1 | 2012-01-19 14:47:03 |
|      1 |        2 | 2012-01-19 14:48:12 |
|      1 |        3 | 2012-01-19 14:48:15 |
|      2 |        1 | 2012-01-19 14:49:33 |
|      2 |        1 | 2012-01-18 14:49:42 |

And then calculate the daily tallies with a query:

SELECT UserID,
       ActionID,
       DATE(Time) AS Date,
       COUNT(*) AS n
FROM actions
GROUP BY UserID,ActionID,Date
ORDER BY Date,UserID,ActionID;

+--------+----------+------------+---+
| UserID | ActionID | Date       | n |
+--------+----------+------------+---+
|      1 |        2 | 2012-01-17 | 2 |
|      1 |        3 | 2012-01-17 | 2 |
|      3 |        2 | 2012-01-17 | 6 |
|      1 |        1 | 2012-01-18 | 1 |
|      1 |        2 | 2012-01-18 | 1 |
|      1 |        3 | 2012-01-18 | 4 |