CODEWITHSUNDEEP
phpmysqlsql
Dan P.
Dan P.

Reputation: 1775

Select records from table1 depending on table2

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) ?

Upvotes: 1

Views: 269

Answers (2)

Veerendra
Veerendra

Reputation: 2622

You can use mysql join to get data from one table based on the another

In the upper case you can have join on the first tables id with the other tables user1_id, user2_id putting where clause on the liked, passed, matched

To know more about mysql joins

Try this as join is much better than a inner query suggested by ersumit $loggedINuser_id='2';//take from session $sql="SELECT tu.id, tu.picture_path, tu.profile_picture, tu.username FROM tusers tu LEFT JOIN secondtable st ON tu.id=st.user2_id WHERE tu.profile_picture IS NOT NULL AND tu.settings LIKE '1,%' AND tu.sex = :sex AND tu.last_visit BETWEEN CURDATE() - INTERVAL 21 DAY AND CURDATE() AND tu.dob BETWEEN :dob - INTERVAL 5 YEAR AND :dob2 + INTERVAL 5 YEAR AND st.user1_id != '".$loggedINuser_id."' LIMIT 1";

Upvotes: 0

hangman
hangman

Reputation: 875

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

Upvotes: 1

Related Questions