$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.
精彩评论