SELECT...FOR UPDATE with prepared statement is not working

Question

I am trying to create a fb-like-button function(not facebook api). There are two buttons on my website: like and dislike. Database will save how many likes and dislike in total.

Here is the db table:

id |post_id |like_count
40 |     20 |         0

code for when user click like button:

$id = 40;
$conn->autocommit(FALSE);
$conn->query("BEGIN;");

//lock the row to prevent race condition
$sql = "SELECT like_count FROM post_like WHERE id = ? LIMIT 1 FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

//update the table
$sql = "UPDATE post_like SET like_count = like_count + 1 WHERE id = ? LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

$conn->commit();
$conn->autocommit(TRUE);
$conn->close();
//when success, display unlike button to user throught AJAX

and code for when user click unlike button:

$id = 40;
$conn->autocommit(FALSE);
$conn->query("BEGIN;");

//lock the row to prevent race condition
$sql = "SELECT like_count FROM post_like WHERE id = ? LIMIT 1 FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

//update the table
$sql = "UPDATE post_like SET like_count = like_count - 1 WHERE id = ? LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

$conn->commit();
$conn->autocommit(TRUE);
$conn->close();
//when success, display like button to user throught AJAX

here is the problem...
number of like_count starts at 0.
Theoretically, if there is only one person click the button, like_count will not exceed 1 or less than 0.
(click like button->->lock row->like_count+1->release row->display unlike button)
(click unlike button->lock row->like_count-1->release row->display like button)
I get this work right when I click the button slowly, however, when I keep on clicking the button quickly, number of like_count can exceed 2, and sometimes it can be a negative number.
I have no clue what I have done wrong. Please help!

Answer 1

in your update query put

$stmt->execute();

below

$stmt->bind_param('i', $id);

Answer 2

The code to handle all the buttons.

$amount = (isset($_POST['like'])) ? 1 : -1;
$sql    = "UPDATE post_like SET like_count = like_count + ? WHERE post_id = ?";
$stmt   = $conn->prepare($sql);
$stmt->execute(array($amount,$_POST['id']));

note that you don't need an id column in this table. post_id is all right

Answer 3

Change:

$conn->autocommit(FALSE);

To:

$conn->autocommit(TRUE);

update and insert query doesn't work with $conn->autocommit(FALSE);