Reza Reza - 3 months ago 6
MySQL Question

Insert a row if it does not exists with the same values

I would like to insert a new row to my MySQL table kh_comments but it should only be inserted if a row with the same values does not exist.

This is how I tried it and I know that it is completely wrong:

public function prepare($author, $arr) {
$conn = new mysqli($this->servername, $this->username, $this->password, $this->db_name);

foreach ($arr as $value) {

$stmt = $conn->prepare("INSERT INTO kh_comments WHERE NOT EXISTS (author, abstract) VALUES (?, ?)");
$stmt->bind_param("ss", $author, $value['id']);
$stmt->execute();

$stmt->close();
$conn->close();
}
}


I also tried using
INSERT IGNORE INTO ...
but that also didn't work for me.

Any help would be appreciated!

EDIT:

This is how my table looks like:
enter image description here
In my SQL code I am NOT defining the
ID
. Would that be needed? If so, how the SQL will look then?

Answer

Use INSERT IGNORE

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

$stmt = $conn->prepare("INSERT IGNORE INTO kh_comments(author,abstract) VALUES (?, ?)");

$stmt->bind_param("ss", $author, $value['id']);
$stmt->execute();

$stmt->close();
$conn->close();

Note that for this approach (or the REPLACE INTO approach or even the ON DUPLICATE KEY approach) to be successfull you need to have a unique index on the fields that you don't want duplicated. As mentioned in the extract from the documentation above

Comments