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 ?
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)
);
update tbl set counter = counter + 1
