join data with mysql and php

i have 2 tables:

 TABLE USER
ID | USER | PASSWORD
1 | john | xxx
2 | max | xxx
3 | carl | xxx
4 | marie | xxx
5 | andy | xxx


TABLE FRIENDS
ID | iduser | idfriends
1 | 1 | 2
2 | 1 | 3
3 | 2 | 1
4 | 2 | 4
5 | 2开发者_运维知识库 | 5
6 | 2 | 3 
7 | 3 | 1 

how can i get all friends johns?

Try this:

SELECT u.* FROM FRIENDS f
INNER JOIN `USER` u
ON f.idfriends=u.id
WHERE f.iduser=1;

SELECT u.user 
FROM user u 
LEFT JOIN friends f ON u.id=f.idfriends 
WHERE f.iduser = (SELECT id FROM user WHERE user = 'john');