3

I am working on project with php-MySQL in the back-end.It has various articles to browse,sorted into various categories and a user-login system.
I recently added a watch-list functionality to it which enables users to add articles to their watch-list/dashboard.
I have the following tables.

Articles table
article-id(int) Primary-Unique
cat(varchar)
name(varchar)
subCat(varchar)
description(varchar)
date added

users table
username(varchar) Primary-Unique
lname
fname
joined-date

.Watchlist table
article-id(int)
username(varchar)
date_watch

As we can see there is no unique key for watch-list table
I want to make (username + article-id) a unique pair Because for every username more than 1 article-id's exist and viceversa
I just want to insert and delete rows and It's not needed to update them
how to prevent duplicate entries?
till now I have been checking number of rows with php 

"SELECT * FROM watchlist WHERE article-id={$id} AND username={$user}"

and if 

mysql_num_rows() >1(not allowed to insert)

This works fine but if by mistake that INSERT command is executed thare will be a duplicate entry
How to prevent it?
I am using phpmyadmin

arulmr
  • 8,620
  • 9
  • 54
  • 69
Srikanth Muttavarapu
  • 756
  • 1
  • 10
  • 13
  • You may find your answer here: http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql. – Sutandiono Aug 23 '13 at 05:25

3 Answers3

8

You can simply add a unique key to the table:

ALTER TABLE `watchlist`
ADD UNIQUE INDEX `watchlist_unique` (`article-id`, `username`);

Also, looking at the functionality of what you have, you may as well opt for setting it as your primary key, by using this instead of the above:

ALTER TABLE `watchlist`
ADD PRIMARY KEY (`article-id`, `username`);

Both will prevent any insertion of a duplicate entry.

In addition, if you want to insert in this case, you may want to check out INSERT IGNORE and ON DUPLICATE KEY. For more info on both see "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE".

Community
  • 1
  • 1
vollie
  • 1,005
  • 1
  • 11
  • 20
  • 1
    one thing to be noticed is that if any unique index have defined on `article-id` or `username` before this, they must be removed to allow this rule works well for cases such as `ON DUPLICATE KEY UPDATE` – VSB Sep 06 '14 at 12:44
1

Try like

$sql =  "SELECT * FROM watchlist WHERE article-id={$id} AND username={$user}";
if(mysql_num_rows($sql) >=1) {
     //Prevent Insert 
} else {
     //Put your Insert query
}

Or simply use IGNORE like

INSERT IGNORE INTO watchlist
    (article-id, username)
VALUES
    ({article-id}, {username}),

Makesure that you are using the columns as UNIQUE constraints.If not change them as like

ALTER TABLE `watchlist`
ADD UNIQUE INDEX `watchlist_unique`
(`username`,`article-id`);

You need to check atleast of 1 record not greater than 1.

And try to avoid mysql_* statements due to the entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_*, is officially deprecated as of PHP v5.5.0 and will be removed in the future.

There are two other MySQL extensions that you can better Use: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql.

GautamD31
  • 28,552
  • 10
  • 64
  • 85
  • If you read his original posting, your solution is exactly what he tried. He wants an insurance in case he mistakenly inserts the same details into his database. – Sutandiono Aug 23 '13 at 05:31
  • @Sutandiono have you checked my ans entirely..??I gave diff solutuion from him. – GautamD31 Aug 23 '13 at 05:32
  • I have told him to check with `>=` and also using `IGNORE`..see it – GautamD31 Aug 23 '13 at 05:34
  • Okay, I missed the `=` sign at `>=`. As for `INSERT IGNORE`, you will still need to add the `UNIQUE` constrains which tells the DB which columns are considered unique. – Sutandiono Aug 23 '13 at 05:42
  • @Sutandiono thanks for your suggestion.I will try to explore MySQLi later.But for now what should I prefer.Checking number of rows or the answer posted by @ellie(create unique pair). Which is better? – Srikanth Muttavarapu Aug 23 '13 at 13:34
  • You should do both. Check the number of rows in PHP to prevent duplicate entry, and add `UNIQUE INDEX` in your database as a backup in case someone later tries to insert data straight into the table, bypassing your PHP script. – Sutandiono Aug 24 '13 at 05:15
  • Yes Iam doing it for both the fields – GautamD31 Aug 24 '13 at 05:16
0

You can make a unique index on username and article-id:

ALTER TABLE watchlist ADD UNIQUE INDEX my_idx ( username, article-id);

Then no duplicate inserts will be permitted.

You can also handle duplicate inserts gracefully with the INSERT INTO ... ON DUPLICATE KEY syntax, to update certain fields in the entry with the given username, article-id pair.

vmayar
  • 1
  • 1