Jump to content


Photo

Inner Join Help

php pdo mysql sql inner join

Best Answer derekshull, 12 December 2013 - 12:12 AM

StackOverflow for the win:
 

SELECT * 
FROM activity f 
INNER JOIN wp_bp_friends n2 
ON n2.friend_user_id=f.user_id 
WHERE n2.initiator_user_id=:userid 
UNION ALL
SELECT * 
FROM activity f 
INNER JOIN wp_bp_friends n2 
ON n2.initiator_user_id=f.user_id 
WHERE n2.friend_user_id=:userid 
ORDER BY datetime DESC LIMIT 8
Go to the full post


  • Please log in to reply
4 replies to this topic

#1 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 11 December 2013 - 02:21 PM

I am trying to get all the "posts" from a users friends list on their news feed.  I'm trying to do so by getting all those posts where the user is the initiator_user_id and get all the posts from the friend_user_id then I want to get all the posts from initiator_user_id where the user's id is the friend_user_id.

This is what I have that's messing up:
 

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n1 ON (n1.initiator_user_id=f.user_id) INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id) WHERE (n1.friend_user_id=:userid) OR (n2.initiator_user_id=:userid) ORDER BY datetime DESC LIMIT 8'); 
$wallsql->bindParam(':userid', $_SESSION['uid']);
$wallsql->execute();
 
 
Previously this works but it only gets the posts where the user is the initiator_user_id:

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n2 ON n2.friend_user_id=f.user_id WHERE n2.initiator_user_id=:userid ORDER BY datetime DESC LIMIT 8'); Any help would be much appreciated!

 



#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 11 December 2013 - 03:19 PM

Can you post your table structures, some sample data and what you expect to be returned from that data?


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 11 December 2013 - 03:33 PM

wp_bp_friends table:

id     initiator_user_id     friend_user_id     is_confirmed     is_limited     date_created

11            24                            22                       1                   0          2013-12-11 11:17:26




Activity table:

ID     user_id                type                       component     filetype     group_id        content                     datetime

15        22             accountcreated                 NULL           NULL            0                NULL                2013-12-11 11:17:26

15        22                   status                          NULL           NULL            0           Hello World!          2013-12-11 11:17:26

15        24            accountcreated                  NULL           NULL             0               NULL                2013-12-11 11:17:26

15        24                   status                          NULL           NULL            0             Whats up            2013-12-11 11:17:26


So with this SQL Statement:

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n2 ON n2.friend_user_id=f.user_id WHERE n2.initiator_user_id=:userid ORDER BY datetime DESC LIMIT 8');

Userid 24 can see userid 22's "posts" such as when 22's account was created and his status update.  What it NEEDS to do is let userid 22 see userid 24's updates as well.  Right now it's one sided.  I thought that this would accomplish what I'm wanting where both users and see each others post updates:
 

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n1 ON (n1.initiator_user_id=f.user_id) INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id) WHERE (n1.friend_user_id=:userid) OR (n2.initiator_user_id=:userid) ORDER BY datetime DESC LIMIT 8'); 
$wallsql->bindParam(':userid', $_SESSION['uid']);
$wallsql->execute();

But it doesn't seem to be working.



#4 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,257 posts
  • LocationMississauga, Canada

Posted 11 December 2013 - 10:07 PM

Then, try something like (not tested):

$wallsql= $conn->prepare('SELECT * FROM activity f 
INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id OR n2.initiator_user_id=f.user_id)
WHERE n2.initiator_user_id = :userid 
ORDER BY f.datetime DESC LIMIT 8'); 
$wallsql->bindParam(':userid', $_SESSION['uid'],PDO::PARAM_INT);
$wallsql->execute();

PS: Perhaps, Barand will give you a better idea tomorrow morning :)



#5 derekshull

derekshull

    Advanced Member

  • Members
  • PipPipPip
  • 107 posts

Posted 12 December 2013 - 12:12 AM   Best Answer

StackOverflow for the win:
 

SELECT * 
FROM activity f 
INNER JOIN wp_bp_friends n2 
ON n2.friend_user_id=f.user_id 
WHERE n2.initiator_user_id=:userid 
UNION ALL
SELECT * 
FROM activity f 
INNER JOIN wp_bp_friends n2 
ON n2.initiator_user_id=f.user_id 
WHERE n2.friend_user_id=:userid 
ORDER BY datetime DESC LIMIT 8





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com