March 18, 2013

After MYSQL query return if the row was updated or inserted

Question by Joris Blanc

So I’m working on a part of my website where users can uprate and downrate content, this is stored one row per rating with a +1 for an upvote and a -1 for a downvote.

I also will be having a total of upvote - downvotes of the users profile page. The total of these votes will be stored in the mysql table of the users information.

My problem is that when someone votes something, lets say a downvote so I substract one to the total value of the votes on the users profile. But then if the user changes his mind and modifies the vote to an upvote well for the final value to be right I have to actually add 2 ( +1 to compensate the -1 and an other +1 for the upvote)

$sql = "    
    INSERT INTO `votes`
    (
        `added_by`, 
        `value`,
        `post_id`,
        `ip_created`
    )  VALUES (
        '".$logedInUser['User_ID']."',
        '".$value."',
        '".$this->post_id."',
        '".ip2()."'
    )
    ON DUPLICATE KEY UPDATE `value` = '".$value."'
";
$db->sql_query($sql);

I know that I can figure out a way to do it, but it will require a few other queries and I’m hopping there is a way to be able to return if it was inserted or updated. (I know that the query will only return TRUE or FALSE)

Answer by Starx

Based on your current data structure, I think instead of the hack of adding 2 to balance the data when the user upvote and subtracting 2 when he downvotes again. Try this:

  1. You have to first remove the vote.
  2. Then cast another vote to make the change.
...

Please fill the form - I will response as fast as I can!