Welcome Guest, Not a member yet? Register   Sign In
Mysql get values from table with join (or and) ?

(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

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)

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]

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

Messages In This Thread
RE: Mysql get values from table with join (or and) ? - by obiron - 02-27-2015, 03:22 PM

Theme © iAndrew 2016 - Forum software by © MyBB