Simple MYSQL Insert (Only if Doesn't Exists)

Hello.

I am good at PHP, but this MYSQL is giving me hell. I am still a newbie at that.

I have a database and a table. The table is called users.

This table contains only 2 columns. Their names are:
ip, points

I am trying to build this simple query that will allow me to insert a new line with the user's IP and increase the points he has by a specified number. There could be 2 scenarios:
1. A line with the "ip" column containing the user's IP does not exist - Create a new line with the user's IP and set the points to 0 (I've already set the default value for the column called "points" to be 0).
2. There is a line with value in "ip" that matches the user's IP - In this case just update the "points" field of this line and increase it by a specified number (the specified number is set below as a variable called $points).

I start by defining the following variables:

$subid = $_REQUEST['subid']; // That's the user's IP
$points = $_REQUEST['points'];

PHP:

I've tried the following query:

mysql_query("INSERT INTO users (ip,points) VALUES ('".$subid."','".$points."') ON DUPLICATE KEY UPDATE users SET points=points+".$points." WHERE ip='".$subid."'");

PHP:

... and numerous others, but had no luck.

 

Not up to even pseudo code tonight.

I think you are going to need to make this a two step process, a query and then an insert. First check to see if the user exists. If yes update.

If no, create the user and an insert whatever you wish to insert.

 

I think this should do it

mysql_query("INSERT INTO users (ip,points) VALUES ('$subid','$points') ON DUPLICATE KEY UPDATE points=points+$points WHERE ip='$subid.'");

Code (markup):

also make sure ip is declared as UNIQUE

 

I get this error:

Also, after the last $subid you have a dot, but before it there is none. I've tried adding a dot before it or removing the surrounding dots, but that didn't work either. I got errors similar to the one above.

"ip" is unique.

 

Hi, you've to remove where clause and quotes around integers:

mysql_query("INSERT INTO users (ip,points) VALUES ({$subid},{$points}) ON DUPLICATE KEY UPDATE points=points+{$points}") or die(mysql_error());

PHP:

Regards

 

Thanks man. Your version gave me the error below, but I've played around with the brackets and apostrophes a little bit and finally got it working.

The error received with your query:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.196.222,) ON DUPLICATE KEY UPDATE points=points+' at line 1

Code (markup):

THE WORKING QUERY:

mysql_query("INSERT INTO users (ip,points) VALUES ('".$subid."','".$points."') ON DUPLICATE KEY UPDATE points=points+'".$points."'") or die(mysql_error());

Code (markup):