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