0
-------------------------------------
| 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.

MrObscure
  • 475
  • 3
  • 17
  • 1
    https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html – Sammitch Jul 07 '21 at 18:20
  • 1
    You code is **vulnerable** to **sql injection** so use only **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Jul 07 '21 at 18:41
  • Does this answer your question? [Insert into a MySQL table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) – He3lixxx Jul 08 '21 at 06:34

1 Answers1

2

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.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175