Complex query

[Hobbyist_PHPer]

5.0.92-community

 

Hi everyone, I'm trying to create a simple match making site, and I've come across a problem in which I can't figure out the logic for...

 

There's the following tables:

Profiles

    ProfileID

    ProfileSexType

 

FantasyTypes

    FantasyType

 

Fantasies

    Fantasy

 

ProfilesSeekingSexTypes

    ProfileID

    ProfileSeekingSexType

 

ProfilesFantasyTypes

    ProfileID

    FantasyType

 

ProfilesFantasies

    ProfileID

    Fantasy

 

The problem comes into when I want to search for profiles that meet selected criteria... So like this first part of the query matches the sex of the person they are looking for and the sex of person that the other person is looking for...

 

                        $FantasyType_Array = $_POST['FantasyType'];
                        $Fantasy_Array = $_POST['Fantasy'];
                        $query = mysql_query("SELECT Profiles.ProfileID FROM Profiles LEFT JOIN ProfilesSeekingSexTypes ON Profiles.ProfileID = ProfilesSeekingSexTypes.ProfileID WHERE Profiles.ProfileSexType = '{$_POST['ProfileSexType']}' AND ProfilesSeekingSexTypes.ProfileSeekingSexType = '{$_POST['ProfileSeekingSexType']}'");
                        while ($row = mysql_fetch_assoc($query)) {
                            $RemainingProfiles_Array[] += $row['ProfileID'];
                            
                        }
                        echo '<pre>';
                        print_r($RemainingProfiles_Array);
                        echo '</pre>';

 

Now the part that I can't seem to figure out the logic for is, how do I narrow those results down to those ProfileIDs that have the "FantasyType" and the "Fantasy" matches? Obviously the other person's profile can contain other criteria, but it must contain at least the criteria that the person is searching for... As you can see from the tables, a profile can have many "ProfilesFantasyTypes" and many "ProfilesFantasies"

[fenway]

Sorry, I don't follow -- what's the problem/