根据table2从table1中选择记录

Question

I am trying to build a project where you can like other people's pictures, and when the other person likes your picture too, you have a match. Like the Tinder app if you know.

Now, I fetch 1 photo like so:

SELECT id, picture_path, profile_picture, username 
       FROM tusers 
       WHERE profile_picture IS NOT NULL
             AND settings LIKE '1,%' 
             AND sex = :sex
             AND last_visit BETWEEN CURDATE() - INTERVAL 21 DAY AND CURDATE()
             AND dob BETWEEN :dob - INTERVAL 5 YEAR AND :dob2 + INTERVAL 5 YEAR
LIMIT 1

However, if you've already LIKED or PASSED someone's photo, I don't want to show it to you again. I am not sure how to do this part yet (right now, I have alreadyLiked() and alreadyPassed() functions and I am only doing a header("Location") redirect if they return true, but that will fail when you have liked/passed all the photos).

I have another table with these columns: id, user1_id, user2_id, liked, passed, matched

When you like or pass a picture, a 1 is inserted in the corresponding column.

user1_id is your ID. user2_id is the other person's ID.

Knowing the above information, what kind of query (or logic) would you use to make sure that you only show the right people (that you haven't liked or passed already) ?

Answer 1

suppose you have 2 tables

usr

id  username
1   a
2   b
3   c
4   d

liked

id  user1   user2   liked
1    1       4          1
2    1       3          1

assuming your id is 1 , from table liked it seems you have liked c,d . since 1(a) is your own id you need only b as output, your query goes as below

SELECT * 
FROM usr
WHERE id NOT 
IN (

SELECT user2
FROM liked
WHERE user1 =1
)
and id!=1

assuming 1 will come from session