I have 2 tables:
Table - User
userId || username || profileImage
------------------------------------
1 || aa || aaaaa.png
2 || bb || bbbb.png
3 || cc || cccc.png
4 || dd || dddd.png
Table - Relationship
relationshipId || user1 || user2 || status
-----------------------------------------------
1 || 1 || 2 || 0
2 || 1 || 3 || 1
3 || 1 || 4 || 1
4 || 2 || 4 || 1
So I want the details of all the users (3,4) whom 1 has sent request and status is accepted as 1.
Something I tried :
$stmt = $this->db->prepare("
SELECT users.uId
, users.username
, users.profilePic
from users
JOIN relationship
WHERE (relationship.user_one_Id = :profileUserId || relationship.user_two_Id=:profileUserId)
AND relationship.statusCode=:statusCode
AND users.uId != :profileUserId
LIMIT 6
");
$stmt->bindvalue(":statusCode", 1);
$stmt->bindparam(":profileUserId", $profileUserId);
$stmt->execute();
But this query is giving me rows including those with whom I am not friends too. So where did I go wrong.
I don't understand where relationship.user_one_Id comes into the picture. I assume you meant to write relationship.user1? I also assume that the sender is stored in user1. In which case your query is way too complicated:
SELECT t.uId, t.username, t.profilePic
FROM users t
INNER JOIN relationship r
ON (t.id = r.user2
AND r.user1 = :profileUserId
AND r.status = :statusCode
)