I'm writing sort of a travel-'dating' app.

* Users register themselves
 * Users tell the app if they are male or female
 * Users tell the app which countries they would like to visit
 * Users tell the app if they want to travel with males (pref\_m=1) or females (pref\_f=1)

My tables
---------

**table 1: users**

`id (key) | gender | pref_m | pref_f  

------------------------------------  

 1          male     1        0  

 2          male     1        1`  

**table 2: countryselection**  

`id (key) | userid | countryid   

------------------------------------  

 1          1        123  

 2          1        111  

 3          1        100  

 4          1        110  

 5          2        123  

 6          2        111  

 7          2        202  

 8          2        210`  

So what the select statement has to do
--------------------------------------

**Input:** the userid of the current user  

**Output (in logic):** SELECT the userids AND matching countries OF ALL people that want to travel to the same countries as I do, and want to travel with someone that has my gender  

*(join)* Of that selection I obviously only need the people that are of the gender that I am looking for.  

ORDERED by people that have the most matching countries with me DESC.

What I have so far (warning: not much)
--------------------------------------

`$sql = "SELECT userid,count(*) AS matches from countryselection";  

 $sql .= " WHERE countryid IN (SELECT countryid FROM countryselection WHERE userid = :userid) GROUP BY userid ORDER BY matches DESC;";`
*This gives me a list of all people that want to travel to the same countries as me (and how many countries we have in common)*

final note
----------

I'm obviously struggling with the gender-selection part.  

Not sure if I have done the right thing to store the user selections in the way that I have.  

I might need some guidance there too.  

**Obviously - thanks all.**