CODEWITHSUNDEEP
phpmysqlsql
Reza
Reza

Reputation: 523

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?

Upvotes: 3

Views: 99

Answers (3)

Jayendra Manek
Jayendra Manek

Reputation: 148

$check  = mysql_num_row(mysql_query("select * from  kh_comments where author  =    $auther   and  abstract=$value['id']"));

Upvotes: 0

e4c5
e4c5

Reputation: 53734

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

Upvotes: 4

Tamil
Tamil

Reputation: 1203

You might use REPLACE INTO

As the documentation says:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Upvotes: 1

Related Questions