Edit duplicate if exists mysqli php

Question

I have some troubles with my mysqli INSERT code. I don't really know how to deal with duplicates but I have read many posts there and I tryied to get it work, but when I execute this code with same variables the count of rows increases. Can you please help me fixing this code or find another way to check for duplicates?

  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);
  // Check connection
  if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
  }


  if ($stmt = $conn->prepare(
    "INSERT INTO matka (jmeno, prijmeni, bydliste, telefon, email)
    VALUES (?,?,?,?,?)
    ON DUPLICATE KEY UPDATE
    jmeno = VALUES(jmeno),
    prijmeni = VALUES(prijmeni),
    bydliste = VALUES(bydliste),
    telefon = VALUES(telefon),
    email = VALUES(email)" )) {
  $stmt->bind_param("sssss", $jmeno_matky, $prijmeni_matky, $bydliste_matky, $telefon_matky, $email_matky);
  $stmt->execute();
  printf("%d Row inserted.\n", $stmt->affected_rows);
  $stmt->close();
  $id_matky = $conn->insert_id;
  $conn->close();
  }

Answer 1

ON DUPLICATE KEY relies on there being a PRIMARY or UNIQUE key defined. This means that the column(s) the key is defined on cannot be the same for two rows.

If you for example made an UNIQUE key on email like so:

CREATE INDEX matka_email ON matka (email)

then when you try and insert a record and the email address already exists, it will update the fields as defined in your query after ON DUPLICATE KEY

Note that Unique keys can involve several columns like so:

CREATE INDEX matka_alldata ON matka (jmeno, prijmeni, bydliste, telefon, email)

See docs:

• https://dev.mysql.com/doc/refman/5.7/en/create-index.html