Page 1 of 1

Buddy List?

Posted: Tue Dec 20, 2005 11:19 am
by Dale
How would I go about getting a buddy list working?

Say my database is set like this:

ID
USERNAME
PASSWORD
BUDDIES

(With 3 test values as)

1
DALE
PASSWORD
2,3

2
TEST
TESTING
1

3
MYTOX
MYYIT

Now how would I get it so when user one (DALE) is logged in, it shows his friends (User ID 2 & 3) ??

Posted: Tue Dec 20, 2005 11:28 am
by hawleyjr
You need to use two tables. Also, search this forum for normalization.

Code: Select all

Table 1

ID
USERNAME
PASSWORD

Table 2

USER_ID, BUDDY_ID

Query:

SELECT a.USERNAME,b.USER_ID FROM TABLE2 AS b
LEFT JOIN TABLE1 as a on a.ID = b.BUDDY_ID
WHERE b.BUDDY_ID = $this_user_id

Posted: Tue Dec 20, 2005 11:28 am
by Chris Corbyn
The database structure would be bad for that. You need more tables.

At least two tables for that alone:

users: -
id
username
password

buddies: -
id
userid
buddy_userid


Then lets say the user table looks like this:

Code: Select all

id      |         username      |      password
-------------------------------------------------------------
1      |           dale             | somemd5hash
2      |            bob             |   somehash
3      |            jill                |       anothermd5hash
4      |         Jack               |         password
Dale might be friends with Jack and Jill so the buddies table looks like:

Code: Select all

id   |      userid         |       buddy_userid
---------------------------------------------------
1    |          1             |            3
1    |          1             |            4
So to get the buddies of dale it's just a quick inner join....

Code: Select all

$query = "select a.username from users as a, buddies as b where b.buddy_userid = a.id and b.userid = 1";
;)

Posted: Tue Dec 20, 2005 11:34 am
by Dale
I'll have a mess around with these suggestions. Thank you. :)

Posted: Tue Dec 20, 2005 1:36 pm
by John Cartwright
search for "many to one relationship"

Posted: Tue Dec 20, 2005 4:52 pm
by Chris Corbyn
.... or just relational databases.

Some useful keywords -- This applies to *anyone* wanting to learn databases:

Codd's Rules
ERD's (Entity Relationship Diagrams)
Normailization
Relational Storage