CODEWITHSUNDEEP
phpmysql
MrObscure
MrObscure

Reputation: 529

insert on duplicate key Mysql PHP

-------------------------------------
| id      | address   | keylogs     |
-------------------------------------
| 1       | foo       | 5           |
-------------------------------------
| 2       | bar       | 12          |
-------------------------------------

$keylogs = $_POST['username'];
"INSERT INTO `logger` (`id`, `Address`, `keylogs`) values (Null, '".$address."', 'g') ON DUPLICATE KEY UPDATE `keylogs` = '".$keylogs."'"

the above statement will of course insert a new row but what I'm trying to do is if the column have the same record then don't insert a new row but update the old one

-------------------------------------
| id      | Address   | keylogs     |
-------------------------------------
| 1       | foo       | 5           |
-------------------------------------
| 2       | bar       | 12          |
-------------------------------------
| 3       | baz       | 1           |
-------------------------------------
| 4       | bar       | 1           |
-------------------------------------
| 5       | qux       | 1           |
-------------------------------------

what I'm trying to achieve is:

-------------------------------------
| id      | Address   | keylogs     |
-------------------------------------
| 1       | foo       | 5           |
-------------------------------------
| 2       | bar       | 13          |
-------------------------------------
| 3       | baz       | 1           |
-------------------------------------
| 4       | qux       | 1           |
-------------------------------------

so literally,

if field Address exists, update keylogs, else insert a new record.

is it possible to achieve this with a single insert statement? and thanks.

Upvotes: 0

Views: 49

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76414

The reason your insert always inserts a new record is that a single key is being in use, namely the id. You will need to create a unique constraint on the address in order to have something violated if the address is being duplicated.

EDIT

You will need to ensure that your query is properly sanitized by using PDO or some other tool. Your current code is prone to SQL Injection which stems in the unreasonable trust of user input and users can enter malicious code into the inputs that will end up as parameters of your query.

Upvotes: 2

Related Questions