Inner join wont work as I want

Slampan

Hi!

Im trying to change some part of Srinivas wall script.

For the moment the script only loops out data as I self written.
But I want to see data from my friends to, not only my data.

MySQL-Db:
addressbook
ID
owner
friend
verified

messages
msg_id
message
uid_fk
uploads

users
uid
username

And this code lists all messages, but I want to list my friends message in same row. What am I doing wrong?

$query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username,M.uploads FROM messages M, users U JOIN adressbok ON adressbok.owner='$uid' AND adressbok.verified=1 WHERE M.uid_fk=U.uid AND M.uid_fk='$uid' $morequery order by M.msg_id desc limit " .$this->perpage) or die(mysql_error());

leatherback

For starters, there are typos in your code:

adressbok ON adressbok.
should probably be:

adressbook ON adressbook.

Derokorian

Actually it should probably be addressbook both time, since in his list of tables / columns before the query that's what he wrote. (and also then address wouldn't still be spelled wrong)

leatherback

Derokorian;11001195 wrote:
Actually it should probably be addressbook both time, since in his list of tables / columns before the query that's what he wrote. (and also then address wouldn't still be spelled wrong)

lol; You're right of course.

Slampan

It is adressbok and my code works.
My problem is that my friends messages arent listed.
I must miss something?

I also want to list messages where M.uid_fk = U.friend (and M.owner= me ($uid) except my own messages)

tomhath

This is the same as what you posted. I modified it slightly to fit the way I read queries:

SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username,M.uploads 
FROM users U
JOIN messages M
  ON M.uid_fk=U.uid
JOIN adressbok
  ON adressbok.owner=U.uid
 AND adressbok.verified=1
WHERE U.uid='$uid'
 order by M.msg_id desc

Obviously it's only going to return messages when the message uid is $uid (I assume that means a message from the currently logged in user? rather than to the user?)

Maybe changing to ON adressbok.friend=M.uid_fk will do what you want, but I'm not sure.

Slampan

Sorry, none of this work. It post some messages double.


//	    $query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username,M.uploads FROM users U JOIN messages M ON M.uid_fk=U.uid JOIN adressbok ON adressbok.owner=U.uid AND adressbok.verified=1 WHERE U.uid='$uid' order by M.msg_id desc limit " .$this->perpage) or die(mysql_error());
	    $query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username,M.uploads FROM users U JOIN messages M ON M.uid_fk=U.uid JOIN adressbok ON adressbok.friend=M.uid_fk AND adressbok.verified=1 WHERE U.uid='$uid' order by M.msg_id desc limit " .$this->perpage) or die(mysql_error());

Slampan

Its like a facebook wall. I want to show friends messages from addressbook to. (in adressbok, owner is $uid and friend is the one whos messages I want to se.
If friend is verified by 1