Inner join wont work as I want
Results 1 to 8 of 8

Thread: Inner join wont work as I want

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    4

    Inner join wont work as I want

    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?

    Code:
    $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());

  2. #2
    Beware: Crazy Scientist leatherback's Avatar
    Join Date
    Mar 2002
    Location
    Small border town between Netherlands and Germany
    Posts
    5,395
    For starters, there are typos in your code:

    adressbok ON adressbok.
    should probably be:

    adressbook ON adressbook.
    Php 4.* to 5.*. A small step for programmers. But a leap for me!

  3. #3
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,740
    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)
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  4. #4
    Beware: Crazy Scientist leatherback's Avatar
    Join Date
    Mar 2002
    Location
    Small border town between Netherlands and Germany
    Posts
    5,395
    Quote Originally Posted by Derokorian View Post
    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.
    Php 4.* to 5.*. A small step for programmers. But a leap for me!

  5. #5
    Junior Member
    Join Date
    Apr 2012
    Posts
    4
    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)

  6. #6
    Senior Member
    Join Date
    Jan 2002
    Posts
    2,126
    This is the same as what you posted. I modified it slightly to fit the way I read queries:
    PHP Code:
    SELECT M.msg_idM.uid_fkM.messageM.createdU.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.

  7. #7
    Junior Member
    Join Date
    Apr 2012
    Posts
    4
    Sorry, none of this work. It post some messages double.
    Code:
    //	    $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());

  8. #8
    Junior Member
    Join Date
    Apr 2012
    Posts
    4
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •