(02-26-2015, 01:41 AM)Marcel Wrote: Hi i have the following tables
table buddies |id|b1|b2|status|
(note status = 0/1/2 pending/accepted/blocked) also b1 always is the userid sending the request.
table messages |id|userid|message|
table accounts |userid|name|email|
basically id like to show all messages from 'table messages' that have a buddies status on 1 and are my buddies knowing that in 'table buddy' my userid can be in either b1 or b2 (not in both at the sames time) depending if i request or buddy has requested .
hope im clear on this
thanks
Hi Marcel, You have already had a suitable reply that answers your question, but I would like to point out that this is a really bad database design. You would do better to have a table for BuddyLists, a table for Users and a table for UserBuddyList.
BL: BL_ID (int)
BL_ID=>1
BL_ID=>2
User: UserID (int), Name (char), dob (date) etc..
UserID=>1, Name=>"bob", dob=>2004-04-01
UserID=>2, Name=>"jim", dob=>1984-08-15
UserID=>3, Name=>"max" dob=>1971-06-25
UserBL: BL_ID (int) [FK to BL.BL_ID], UserID (int) [FK to User.UserID]
BL_ID=>1,UserID=>1
BL_ID=>1,UserID=>2
BL_ID=>2,UserID=>1
BL_ID=>2,UserID=>3
Bob is friends with Jim (BL_ID = 1)
Jim is friends with Bob (BL_ID = 1)
Bob is friends with Max (BL_ID = 2)
Max is friends with Bob (BL_ID = 2)
Max might know Jim because they both know Bob
Bob has 2 friends (count UserBL where UserID = 1)
You can extend this so that you can have more than 2 UserBLs for a BuddyList (i.e. Google circles)
Read up on 3rd Normal form - it is your friend when you are building databases