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) ??
Buddy List?
Moderator: General Moderators
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- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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:
Dale might be friends with Jack and Jill so the buddies table looks like:
So to get the buddies of dale it's just a quick inner join....

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 | passwordCode: Select all
id | userid | buddy_userid
---------------------------------------------------
1 | 1 | 3
1 | 1 | 4Code: Select all
$query = "select a.username from users as a, buddies as b where b.buddy_userid = a.id and b.userid = 1";- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia