开发者

$query -> get all 'friends' from user, works but get double times each friend

basicalli, i have a table: friends [id, fromid, toid] [fromid] and 开发者_开发问答[toid] represents the id of the user, fromid is who asked, and toid who acepted.

I consider two users 'friends' where ther is to items in friends table for each.

example: user 1 is friend of user 2 when i have:

in table friends:[id,1,2],[id,2,1],....

Im trying with this query and as i said in title gets me the 'friens', but two times :S

 $sqlQueryCat5 = mysql_query("SELECT friends.*,usuarios.alias AS nombre_amigo  FROM friends LEFT JOIN usuarios ON friends.toid=usuarios.id AND friends.fromid='$this->id' ORDER BY id");

I don't know why.. and you?

thank you


It looks like you have another query that you are not showing here.
This query on its own only shows friends once, because friends.fromid='$this->id' means fromid can only be the id asked.

If your table ALWAYS contains [1,2] and [2,1] (both), then you only need to query one side to get the other.

SELECT friends.*,usuarios.alias AS nombre_amigo  
FROM usuarios
INNER JOIN friends ON friends.fromid=usuarios.id
WHERE usuarios.id='$this->id' 
ORDER BY id


Since your JOIN is not limiting the results of your query (as it is a LEFT JOIN and not an INNER JOIN), I'm guessing you want to move the friend limiter into your WHERE clause, like so:

$sqlQueryCat5 = mysql_query("SELECT friends.*, usuarios.alias AS nombre_amigo "
. "FROM friends "
. "LEFT JOIN usuarios ON friends.toid=usuarios.id "
. "WHERE friends.fromid=$this->id"
. "ORDER BY id");

Edit: looking at your query again, I'm guessing that you don't actually have a reason to use a LEFT JOIN - turn it into a regular JOIN and you could at the very least see a bit better performance.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜