CODEWITHSUNDEEP
mysqljoin
carlgcode
carlgcode

Reputation: 255

Help with mysql join

This is my statement for me to pull all the data of my users' friends that they have added:

$q = $dbc -> prepare("SELECT a.* FROM accounts a INNER JOIN friends fr ON (a.id = fr.friend_id) WHERE fr.id = ?");
$q -> execute(array($details['id']));

Now this is basically where the id matches in the friends table, pull the friends id and all the relevant data with it.

I am trying to implement a friends online page as well, how would I also test to see if a column in accounts for the friend_id matches a certain criterion?

This is how I pull all the users that are online...

$online_users = time() - 900;
$q = $dbc -> prepare("SELECT * FROM accounts WHERE last_active > ? && id != ? ORDER BY id");
$q -> execute(array($online_users, $details['id']));

Upvotes: 0

Views: 62

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

SELECT a.*
  FROM accounts AS a
  JOIN friends  AS f ON (a.id = f.friend_id)
 WHERE f.id = ?
   AND a.last_active > ?
 ORDER BY id

You don't need to repeat the condition on a.id since the join enforces it.

Upvotes: 3

Related Questions