rib rib - 2 years ago 185
SQL Question

Update or Insert, Auto increment int in sql

I'm trying to auto increment (update) or insert the data in my MySQL database, so everytime the PHP file gets requested it should update Uses or insert the DeviceID and Uses, that's working fine with this piece of code, but how can I do it that it increments Uses everytime? I hope you understand my problem.

$sql = "INSERT INTO user (`deviceid`, `uses`) VALUES ('$DeviceID', $Uses)
ON DUPLICATE KEY UPDATE `uses` = '$Uses'";

Answer Source

You want to let MySQL manage the change to the field like this:

$sql = "INSERT INTO user (`deviceid`, `uses`) VALUES ('$DeviceID', 1)
ON DUPLICATE KEY UPDATE `uses` = `uses + 1`"; 

Note: I removed $Uses from the statement, it doesn't make any sense. If you're inserting for the first time it should be 1. If it exists it will be set by MySQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download