Array in a query?

[tekrscom]

MySQL - 5.0.81

PHP - 5.2.5

 

Hi everybody, I'm really stumped on this one...

 

I have a table for members, a table for interestTypes, and a table for memberInterests which consists of 2 columns, the memberID and the interestTypeID

 

So my dilemma is when a person selects a set of interests that other users have, how do you query that? Because each entry in the table is a new row, it seems that I'd have to somehow put the query in a loop from the passed array of interests and put the results in an array? So very confusing... Here's what I have...

 

$ParametersQuery = "";
foreach ($_SESSION[searchParameter]['InterestID'] as $key => $value) {
$ParametersQuery = $ParametersQuery."MemberInterests.InterestID = '$value' AND ";
}
$ParametersQuery = (($ParametersQuery == '') ? '' : '('.substr_replace($ParametersQuery," ",-5).')');

$TempPrivacySetting = $_SESSION[searchParameter]['PrivacySetting'];
$TempGender = $_SESSION[searchParameter]['Gender'];

if ($TempPrivacySetting == 'PublicProfiles'){$TempPrivacySetting=" AND Members.ProfilePrivate = '1'";}else{$TempPrivacySetting="";}

if ($TempGender != 'Either'){
$query = "SELECT Members.* FROM MemberInterests RIGHT JOIN Members ON MemberInterests.MemberID = Members.MemberID WHERE " . 
"Members.Gender = '$TempGender' $TempPrivacySetting AND $ParametersQuery ORDER BY LogOnDate DESC";
}
if ($TempGender == 'Either'){
$query = "SELECT Members.* FROM MemberInterests RIGHT JOIN Members ON MemberInterests.MemberID = Members.MemberID WHERE " .
"Members.Gender != 'Blank' $TempPrivacySetting AND $ParametersQuery ORDER BY LogOnDate DESC";
}
$results = mysql_query($query);
if (!$results) {
    echo '<p>Sorry, there were no results that match your criteria.</p>';
}
else {
    while ($row  =  mysql_fetch_array($results))    {

 

So if the searching user selects say, Gender=Female and ProfilePrivate=1 and then selects say, 4 different interest types, here's what the query ends up looking like...

 

SELECT Members.*, MemberInterests.* FROM MemberInterests RIGHT JOIN Members ON MemberInterests.MemberID = Members.MemberID WHERE Members.Gender = 'Female' AND Members.ProfilePrivate = '1' AND (MemberInterests.InterestID = '13' AND MemberInterests.InterestID = '15' AND MemberInterests.InterestID = '23' AND MemberInterests.InterestID = '24' ) ORDER BY LogOnDate DESC

 

If only 1 interest is selected, the query works... so what I was thinking is that because the query only runs once, it can't search the interests table for each selected matching interest...

[artacus]

You should examine the difference between AND and OR. Probably the easiest answer would be to convert your PHP array to a string that you can use with IN ()

 

$sql = 'SELECT *
FROM memberInterests mi
WHERE mi.interestID IN (' . implode(',', $_SESSION[searchParameter]['InterestID']) . ')'