Incrementing a click counter in MySQL

Question

How do you increment a value in a mysql table by one each time for example if a link is pressed ?

Do you get the data from the database and then add 1 to it and then send it back ? I assume that would use a lot of bandwith.

Any examples and help would be appreciated,

Thank you!

---

Added:

I would also like to keep track of who has pressed the link in the database itself, would I have to add the users ID who has clicked the link so they cannot click it twice kind of thing ?

Answer 1

You can simply use an UPDATE statement like this to increment a counter field:

UPDATE your_table
SET    your_counter = your_counter + 1;

If you would like to keep track of who clicked the link, you'd have to create another table, possibly containing a timestamp field, a user_id field, and a link_url (or link_id) field. Then you can simply insert a new row into this table whenever someone clicks on a link:

INSERT INTO   clicks  (click_timestamp, user_id, link_url)
VALUES        (NOw(), 100, '/news.html');

Note that the NOW() function returns the current date and time.

If you would like to add a constraint such that users cannot click on a link twice, you can set up a composite primary key on (user_id, link_url). The unique constraint that comes with the primary key ensures that you cannot have the same link associated with a particular user more than once. Therefore, this is how your clicks table could look like:

CREATE TABLE clicks (
   user_id          int,
   link_url         varchar(255),
   click_timestamp  datetime,
   PRIMARY KEY (user_id, link_url)
);

Answer 2

update tbl set counter = counter + 1