counting the number of updates to a row in my mysql table

Question

I have a simple mysql DB and use this PHP code to update it.

mysql_query("REPLACE INTO `$db_table` (username, live, datetime, ip) 
VALUES ('$username', '1', '$timeofentry', '$ip')");

I use REPLACE INTO along with a primary key on "username" to let users bump themselves to the top of the most recent list...

I would like to add a bump count. The number of times an entry has been updated (or "replaced into").

How would I go about doing this?

Thanks a lot!

Answer 1

First, you need to add another column to your table to keep the count.

Second, you should probably use the UPDATE statement instead of REPLACE.

REPLACE will actually delete the row, then INSERT a new one which isn't very efficient.

UPDATE `$db_table` SET datetime = NOW(), ip = '$IP', 
        bumpCount = bumpCount + 1 WHERE username = '$username' LIMIT 1;

Answer 2

You can use INSERT ... ON DUPLICATE KEY UPDATE which performs an actual update of existing rows.

$mysql = mysql_connect(..
...
$username = mysql_real_escape_string(...
$ip = mysql_real_escape_string(...
...
$query = "
  INSERT INTO
    `$db_table`
    (username, live, datetime, ip) 
  VALUES
    (
      '$username',
      '1',
      '$timeofentry',
      '$ip'
    )
  ON DUPLICATE KEY UPDATE
    ip = '$ip',
    bumpCount = bumpCount + 1
";

$result = mysql_query($query, $mysql);

Answer 3

@dot

You'd define your bumpCount field as another column in the table. I'd recommend setting it to a default value as well.

Then your table definition would be sometime like:

CREATE TABLE my_table
 (username varchar(255) not null primary key,
  live int,
  datetime datetime not null,
  ip varchar(15) not null,
  bumpCount int unsigned not null default 1);

And your insert/update would be something like:

INSERT INTO my_table (username,live,datetime,ip) 
  VALUES
    ('$username',1,now(),'$ip')
  ON DUPLICATE KEY UPDATE datetime=now() ip='$ip', bumpCount=bumpCount + 1;