How to prevent duplicate data being passed

Question

The question has been answered, thanks

Give it a unique constraint. https://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html — chris85, Aug 02 '15 at 20:55
probably want to start planning your transition away from mysql_* soon — , Aug 02 '15 at 20:55
*"why move from mysql?"* - http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Funk Forty Niner, Aug 02 '15 at 20:58
There are many threads and tutorials on how to switch over to `mysqli` or `pdo`. — chris85, Aug 02 '15 at 20:58
@chris85 yes the native drivers are way better performance and your right in saying that but still that doesn't answer the question. — Jordan Davis, Aug 02 '15 at 21:01
to stick with the question in hand, you can use DISTINCT in SELECT and/or `GROUP BY col` — Funk Forty Niner, Aug 02 '15 at 21:02
@JordanDavis my first comment answers how to avoid duplicates in the table. — chris85, Aug 02 '15 at 21:03
Thanks for your help, but could you give me an example, I am very new to php and mysql — Barry McDaid1982, Aug 02 '15 at 21:03
`SELECT DISTINCT foo, col_x FROM table GROUP BY xxx` possibly with a `WHERE` clause - `WHERE col_x = 'xyz'` - some are optional here. — Funk Forty Niner, Aug 02 '15 at 21:04
yeah I just seen that, I have been editing the code recently — Barry McDaid1982, Aug 02 '15 at 21:06
@chris85 I highly doubt that is the problem... possibly though... — Jordan Davis, Aug 02 '15 at 21:16
@BarryMcDaid1982 my question to you is why are you generating a list like this, if it's a fixed list with set drugs why don't you just specify it in HTML... if you want it to be dynamic updated based on another value such as a category of drug why don't you use AJAX. — Jordan Davis, Aug 02 '15 at 21:18
There are only 10 drugs in the table, it is only a prototype at the moment, so there are no categories, just a list — Barry McDaid1982, Aug 02 '15 at 21:19
@chris85 nah the unique the driver fine and I recommend but just unrelated. What I'm saying is why would anyone list duplicate values in the DB, if your pulling the values from the DB.... but really the question is why would you being doing that if the first place when you could pull then from a static JSON object or even specify it simply in HTML, read that comment I just posted above this. — Jordan Davis, Aug 02 '15 at 21:21
@JordanDavis New products can always be added so it makes sense to use a DB instead of static values. The `unique` won't allow duplicates in the column so it should be used, that is what it is for. — chris85, Aug 02 '15 at 21:29
@chris85 If thats what he is trying to accomplish sure but it doesn't make sense to allow a user to create imaginary drugs... there is a set list of around 1,500 drugs approved by the FDA . Why wouldn't you just pull in the list and all the associated info through in open-source API then use that as your list... Here is a link to a open-source drug database where you can pull all drug info --> http://www.drugbank.ca/documentation — Jordan Davis, Aug 02 '15 at 22:28

score 2 · Accepted Answer · answered Aug 02 '15 at 21:06

2

You can do something like that :

ALTER TABLE  `table_name` ADD UNIQUE ( `column_that_should_be_unique` )

Or you can check it in PHP using :

$query = mysqli_query($con, "SELECT * FROM table_name WHERE column_name='".$user_input."'");

if(mysqli_num_rows($query) > 0){
    //block user from inserting data
}

Or you can do both to ensure that the database will have no duplicates

answered Aug 02 '15 at 21:06

GitCommit Victor B.

526
3
14

Second example opens OP to sql injections. – chris85 Aug 02 '15 at 21:09
Thank you, can I ask how does that check to see if it is duplicated? – Barry McDaid1982 Aug 02 '15 at 21:10
Would I insert the php line before or inside the while statement? – Barry McDaid1982 Aug 02 '15 at 21:12
When you add a new drug, the query search your database to see how many drug with the name $user_input it has. If there is one or more then block the user from inserting a new drug. The best way is to prevent the user from ever inserting duplicate content when inserting new drugs instead of displaying only unique drugs. – GitCommit Victor B. Aug 02 '15 at 21:14
Or you can do like that : array_unique($r1['drug_name']) to remove duplicates from your array. See : http://php.net/manual/fr/function.array-unique.php – GitCommit Victor B. Aug 02 '15 at 21:16
Also use prepared statements don't use the SQL provided here. User input should never be inputted directly into SQL. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – chris85 Aug 02 '15 at 21:16

How to prevent duplicate data being passed

1 Answers1