Link together a friend and user table (to get usernames of friends?)

My user table is not out of the ordinary, and uses the primary id field as a user id.

My friends table looks like this however:

CREATE TABLE `friends` (
  `uid` INT NOT NULL ,                       
  `fid` INT NOT NULL ,                  
  PRIMARY KEY (`user_id`, `friend_id`)   
) ENGINE = MYISAM ;

I can easily find the friends of one user, i.e.

SELECT fid FROM friends WHERE uid = 12

And this would basically return a list of friends in id form, however I am not sure which method at all on how to get their user names or other data. I have heard of foreign keys (and of course joining), am not sure how to actually choose what I need to do.

Right now I do this ugly method:

SELECT username FROM users WHERE id IN (1,2,3,4..(开发者_如何学编程other fid's)..)

It does not seem right at all.

How should I do it?

---

Would be easier if you've posted your user table too. Assuming you have a user table consisting of the fields: id, username, e-mail ... whatever, you can do the following simple join:

SELECT * FROM friends
INNER JOIN users
ON users.id = friends.fid
WHERE friends.uid = 1;

What this do is of course selects the friends records which meets the WHERE criteria, and from those records finds the appropriate row in the users table according to the relationship defined in the ON clause. (Technically in the db engine it works a little different though) In the resulting set a row will include all columns from both friends and users table, but you can modify the columns in result by define the SELECT like:

SELECT friends.uid as uid, friends.fid as fid , users.username as username 

You don't need to explicitly define a foreign key in friends table to do a simple join like this. But doing so helps you keep track of the relationships between the tables, and furthermore you can make MySQL automatically keep your database consistent when deleting or updating rows. http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html .

---

you should use JOIN. you should join friends table with users table:

select
  f.fid,
  u.name as friend_name
from
  friends f
join
  users u on u.id = f.fid and f.uid = 16

---

SELECT 
      u.username, 
      f.fid 
FROM 
      friends f, 
      users u 
WHERE 
      f.uid = 12 and 
      u.uid = f.fid

---

First of all, don't think the primary key is valid in the create table you provided

PRIMARY KEY (`user_id`, `friend_id`) 

Those columns do not exist in your table.

Second:

SELECT users.username FROM friends,users WHERE users.id=friends.fid