Welcome Guest, Not a member yet? Register   Sign In
Make table with two record unique
#1
Wink 

Hello
I have below table
[Image: 2lny1s3.jpg]
In this table we can add
sUsername: omid
sProductID: 1
----------------
sUsername: omid
sProductID: 2
----------------
sUsername: omid
sProductID: 1

in high record i add two same record
Actually i need that insert unique record in table
Username and sProduct can be duplicate
But we cannot have two same record

example :
sUsername: omid
sProductID: 1
----------------
sUsername: omid
sProductID: 2
----------------
sUsername: jack
sProductID: 1
----------------
sUsername: jack
sProductID: 2

In high record different record for each username
Reply
#2

this is very simple.
this case call composite primary key. you can google for this.

create new table 
tbl_user_product_relation 
sUsername Primary Key char(8)
sProductID primary key int(11)
sDatetime timestamp current_timestamp

try to add entry directly from php myadmin
sUsername : tony
sProducID: 1

sUsername: tony
sProductID: 2

sUsername: joe
sProductID: 1

all above will successfull. and below will not allowed

sUsername: tony
sProductID: 1


this is what do you want, right?

in PHP you need to check this 
select * from tbl_user_product_relation where sUsername=? and sProductID=?
if num_rows() == 1 Insert
else echo 'duplicate'

NOTE:

Quote:hei.. what about my sID?

you dont need that. What you know about table is: every table must have id as primary key and auto_increment
Nooooo... that's totally wrong.
and if you pretend to not remove sID, you will have 3 composite primary key, and you will always have duplicate possibility,

because:
sID sUsername sProductID
1 tony 1
2 tony 1
3 tony 1
100 tony 1

is different
Reply
#3

(12-16-2017, 10:58 PM)plonknimbuzz Wrote: this is very simple.
this case call composite primary key. you can google for this.

create new table 
tbl_user_product_relation 
sUsername Primary Key char(8)
sProductID primary key int(11)
sDatetime timestamp current_timestamp

try to add entry directly from php myadmin
sUsername : tony
sProducID: 1

sUsername: tony
sProductID: 2

sUsername: joe
sProductID: 1

all above will successfull. and below will not allowed

sUsername: tony
sProductID: 1


this is what do you want, right?

in PHP you need to check this 
select * from tbl_user_product_relation where sUsername=? and sProductID=?
if num_rows() == 1 Insert
else echo 'duplicate'

NOTE:

Quote:hei.. what about my sID?

you dont need that. What you know about table is: every table must have id as primary key and auto_increment
Nooooo... that's totally wrong.
and if you pretend to not remove sID, you will have 3 composite primary key, and you will always have duplicate possibility,

because:
sID sUsername sProductID
1 tony 1
2 tony 1
3 tony 1
100 tony 1

is different

Oh yes you right
AutoIncrement id is wrong completely
I try now and reply you about it
Thank so much Heart
Reply
#4

Yes it is working now
I remove autoincrement from table and now i dont have any duplicate record
Thanks
Reply
#5

(12-17-2017, 01:53 AM)omid_student Wrote: Yes it is working now
glad to hear that
Reply




Theme © iAndrew 2016 - Forum software by © MyBB