CODEWITHSUNDEEP
phpmysqlpdo
Liam
Liam

Reputation: 9855

WHERE query inside an AND statement? PHP/PDO/MySQL

I have 2 tables on my website, a users table and a user_friendships table each structured as so...

Users

id  |  user_id  |  credits_bank  |  credits_offered

User Friendships

id  |  user_id  |  user_followed_id

When my user logs in he is presented with a list of other users on the website - the list of other users are those who are stored in the users table and have a greater value in the credits_bank table than that of the credits_offered table.

When a friendship is created, the session users id is stored in the user_friendships table, and the id of the other member he followed is also stored in the user_friendships table under the column user_followed_id.

The problem is I now need a query to return all users who have move credits_bank than credits_offered and users that aren't already in the user_frienships table in the same record as the session user.

I'm currently using...

SELECT DISTINCT u.*
FROM users u
    LEFT JOIN user_friendships uf ON u.user_id = uf.user_followed_id
WHERE u.user_id <> ? 
    AND u.credits_offered <= credits_bank
    AND uf.user_followed_id IS NULL

Update

I want to see a list of users whose credits_bank is a greater value than credits_offered and I only want to show them if they dont already exist in a record in my user_friendships table in the same row as my session user.

Users

id  |  user_id  |  credits_bank  |  credits_offered
___________________________________________________
1        123            10                 2
2        231            6                  3
3        312            6                  5
4        213            2                  1

User Friendships

id  |  user_id  |  user_followed_id
___________________________________________________
1       123                231
2       123                312

Result

If session user_id = 123 then...

user_id 231 and 312 WOULDN'T show as they are in the user friendships table alongside session user id
user_id 213 WOULD show as they have more credits_bank than credits_offered and arent in    friendships table

IF the session user_id was 312 then he would see all results as he isnt friends with anybody in the user_friendships table...

Upvotes: 0

Views: 104

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

As far as I can tell, you're close. If the user id of the current user is called SESS_USER_ID, something like this should work for you;

SELECT DISTINCT u.*
FROM users u
LEFT JOIN user_friendships uf 
   ON uf.user_followed_id = u.user_id 
  AND uf.user_id = SESS_USER_ID
WHERE u.credits_offered <= credits_bank
  AND uf.user_followed_id IS NULL
  AND u.user_id <> SESS_USER_ID

(note that SESS_USER_ID is used twice in the query to make it simple)

An SQLfiddle to test with.

Upvotes: 1

Phil Cross
Phil Cross

Reputation: 9302

Try this:

SELECT u.id, u.user_id, u.credits_bank, u.credits_offered 
FROM users u 
WHERE u.credits_bank>u.credits_offered
    AND u.user_id = [ENTER LOGGED IN USERS ID HERE] 
    AND u.user_id NOT IN (
        SELECT f.user_ol
        FROM user_friendships f 
    )

Let me know if you have any issues

EDIT Latest SQL:

SELECT u.id, u.user_id, u.credits_bank, u.credits_offered 
FROM users u 
INNER JOIN user_friendships f 
   ON f.user_followed_id = u.user_id 
       AND u.credits_bank > u.credits_offered 
       AND f.user_id != [CURRENT_USER_ID]
       AND u.user_id != [CURRENT_USER_ID]

Upvotes: 1

Related Questions