To insert unique records, do I have to do something in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

To insert unique records, do I have to do something in PHP

Post by legend986 »

I have a table with four columns... A B C D

I want the B,D combination to be unique while inserting the records. For example, if there was a record already, then I don't want to insert that. Is this a database issue or do I have to do some SELECT in php to see if I get any records and then insert?

Also, how would I use the distinct for multiple columns? At present, if I say SELECT DISTINCT(A), it gives me all records not repeating in column A but I want to do this on multiple columns... Is there a way for this?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Make a unique key that's B,D.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

You mean I should add the unique attribute to B and D? Like this perhaps?

Code: Select all

ALTER TABLE table_name ADD UNIQUE (B,D);
But I thought it'll add B and D as unique and has nothing to do with maintaining unique combination of B and D... Can you advice me further please?
aliasxneo
Forum Contributor
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Post by aliasxneo »

Maybe I'm not thinking straight but even if it did add them separately it wouldn't matter.

Assume B is 1 and D is 2. If you insert that into the db that exact combination can never happen again because B can never be 1 and D can never be 2. Now D can be 1 and B 2, but that's not the same combination.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Careful here - if either B or D are NULL then MySQL won't force uniqueness at all.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Yes... I will take care of the NULL issue... The problem with the approach is something like this: Consider this case:

B,D
1,2
1,3
1,4
2,4

In this case, though B,D are unique, B is not unique... I want a unique combination not unique columns... That is the problem I am currently facing...
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

You need a unique KEY - not a unique column. The syntax is:

Code: Select all

 CREATE TABLE .... UNIQUE KEY `keyname` (`B`,`D`)
Does that work?
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Thank you so much... That did the trick... Never thought that there would be a unique column and a unique key though it makes proper sense now as to why they have this...
Post Reply