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