How can i INSERT OR UPDATE an entry in my database and if updating how can i add to existing values

Question

I have a database keeping track of game records coming in via JSON. If an ID does not exist I simple want to create a new record but if it does exist I want to update the values. So if a player had 10 kills previously and then they played another game and added 10 more kills I would want their total to update to 20 kills based of their ID. Is my code the best way to do it?

<?php 

$con = mysqli_connect("localhost", "root", "","playerstats") or die('Could not    connect: ' . mysqli_error());


error_log("You messed up!", 3, "my-errors.log");

//$raw_data = file_get_contents('php://input');
$raw_data = file_get_contents('test.json');

$data = json_decode($raw_data, true);

$SteamID = $data['SteamID'];
$Username = $data['Username'];
$Kills = $data['Kills'];
$Deaths = $data['Deaths'];
$Rank = $data['Rank'];

 $sql = "INSERT INTO playerinfo(SteamID, Username, Kills, Deaths, Rank)
         VALUES('$SteamID', '$Username', '$Kills', '$Deaths', '$Rank') 
         ON DUPLICATE KEY UPDATE Kills += $Kills, Deaths += $Deaths";

if(!mysqli_query($con,$sql))
{
  die('Error : ' . mysqli_error($con));

}


?>

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 18 '16 at 20:53
IF all else is working properly, if the primary key already exists this code doesn't update the player's rank at all. If their rank could change, you should add it to the statement as well. — Sgt AJ, May 18 '16 at 20:58

score 3 · Accepted Answer · answered May 18 '16 at 20:57

MySQL doesn't have a += operator, you have to use column = column + value. Also, it's not necessary to repeat the $Kill and $Deaths variables, you can use VALUES(columnName) in the ON DUPLICATE KEY UPDATE clause to refer to the value that would have been inserted into that column.

 $sql = "INSERT INTO playerinfo(SteamID, Username, Kills, Deaths, Rank)
         VALUES('$SteamID', '$Username', '$Kills', '$Deaths', '$Rank') 
         ON DUPLICATE KEY UPDATE 
            Kills = Kills + VALUES(Kills), 
            Deaths = Deaths + VALUES(Deaths)";

You should also learn how to use prepared statements instead of substituting variables into the query.

Thank you very much...I will definitely be using prepared statements. — Nick Balistreri, May 18 '16 at 21:04

How can i INSERT OR UPDATE an entry in my database and if updating how can i add to existing values

1 Answers1