Say i have got a similar scenario. I have a page; and i am keeping a count of likes as the user clicks the like button. Now here is my problem.
Say my current count of the page is 50.
My table contains the page_id, and other page_description and also the current total number of likes. Also i have a table LIKES that contain the id of page and the user id.
Now, what my like.php script is doing that it reads the current total count from the table and then updates that table by adding one to it. Also a new entry to the LIKE table is entered.
Say i have 10 users simultaneously liking the page. The script suns and all of the users get 50 as initial count and after adding one; every user updates it to 51 only.
But the real count should have been 50+10=60.
How do i solve this problem?
You could do the addition logic in the SQL script itself. You're running into a concept called "Last write succeeds." This is happening because you're using your PHP page code which is not guaranteed to have any type of read/write lock. You could add that logic, but it would be easier to write your update sql like this:
INSERT INTO tbl_likes (page_id) VALUES (12)
UPDATE TABLE tbl_pages (page_id, like_count) VALUES (12, (SELECT COUNT(page_id) FROM tbl_likes WHERE page_id = 12))
It would be something like that. A particular thing about this algorithm is that you'll want to insert data into the tbl_likes before incrementing the tbl_pages, and that the tbl_pages value is always dependent on the tbl_likes records, and is not some number submitted by a PHP page.
Just a note, there's really no reason to keep the total count in tbl_pages. You should normalize that data as long as your server isn't experiencing huge amounts of load.