Incrementing a mysql count field

Question

I have a count field in a table that I need to increment and this is what I have.

$click_tracker_row = mysql_fetch_array($result);
$current_count = $click_tracker_row['count'];
$new_count = $current_count + 1;
$query_wiki ="UPDATE click_tracker  SET count = '{$new_count}' WHERE click_tracker_id = '{$click_tracker_row['click_tracker_id']}' LIMIT 1";                 
$result = mysql_query($query) ;

But it never changes....is there a better way of doing this and why is this not working count is an integer field

Answer 1

To address the first question is there a better way of doing this, yes. For incrementing a count in MySQL, you do not need to fetch anything as long as you have the id to update and you should also make sure the id is properly escaped for the query.

$id = mysql_real_escape_string($id); // replace $id with however you get the tracker_id
$query_wiki ="UPDATE click_tracker  SET count = count + 1 WHERE click_tracker_id = '{$id}' LIMIT 1"; 

To answer the second question, why is this not working in the lower section you are doing a mysql_query($query) instead of a mysql_query($query_wiki).

Answer 2

$click_tracker_row = mysql_fetch_assoc($result);
$query_wiki ="UPDATE `click_tracker`
              SET `count` = `count` + 1 
              WHERE `click_tracker_id` = '".$click_tracker_row['click_tracker_id']."'         
              LIMIT 1";                 
$result = mysql_query($$query_wiki) ;

Answer 3

You should simply update this counter using SQL and use PHP's mysql_real_escape_string()-function to prevent SQL injection:

$query_wiki ="UPDATE click_tracker SET count = count + 1 WHERE click_tracker_id = '".mysql_real_escape_string($click_tracker_row['click_tracker_id'])."' LIMIT 1";

Furthermore, there is a typo in your mysql_query()-call. You will need to pass $query_wiki to it.

Answer 4

If you have a lot of clicks then incrementing the count with each click can become inefficient because it causes a ton of small database updates. In other words, you want to do this

UPDATE click_tracker SET count = count + 5...

instead of

UPDATE click_tracker SET count = count + 1...
UPDATE click_tracker SET count = count + 1...
UPDATE click_tracker SET count = count + 1...
UPDATE click_tracker SET count = count + 1...
UPDATE click_tracker SET count = count + 1...

Here's an article describing the idea. If you google php rabbitmq you can get an idea for how to build a php version.