CODEWITHSUNDEEP
phpmysqlsql
ShadowStorm
ShadowStorm

Reputation: 851

How to prevent duplicate entries being added to DB table?

I have this PHP/MySQL script which adds a comment into my DB:

$SQL = "INSERT INTO blog_comments (article_id, author_name, comment, path, posted, status) ";
$SQL .= "VALUES (:article_id, :name, :comment, :next_path, Now(), 'Live');";

$STH = $DBH->prepare($SQL);
$STH->bindParam(':article_id', $article_id);
$STH->bindParam(':name', $name);
$STH->bindParam(':comment', $comment);
$STH->bindParam(':next_path', $next_path);
$STH->execute();

Is there any way to modify this so that it doesn't insert the same [author_name], [article_id] and [comment] into this table? I know it's possible for one column by adding UNIQUE to my table, but not sure about multiple columns.

Upvotes: 1

Views: 201

Answers (5)

Arun Killu
Arun Killu

Reputation: 14263

you can also check INSERT ... ON DUPLICATE KEY UPDATE dev.mysql.com

Upvotes: 1

jlibert
jlibert

Reputation: 155

I guess you could check the db for a result before inserting data

Upvotes: 1

Andrew
Andrew

Reputation: 840

You can make a unique key of multiple fields

Upvotes: 1

Byung Kyu Kim
Byung Kyu Kim

Reputation: 71

call those columns as composite primary key.. that way, they will have a unique entry.

Upvotes: 1

John Woo
John Woo

Reputation: 263883

you can add a UNIQUE constraint for multiple columns

CREATE TABLE
(
.....
CONSTRAINT tab_uq UNIQUE (author_name, comment)
)

or by altering the existing table,

ALTER TABLE myTable ADD UNIQUE (author_name, comment);

Upvotes: 5

Related Questions