Array problem

[Hobbyist_PHPer]

Hello everyone... I have spent about 2 weeks trying to solve this problem... Here we go...

 

I have an array of ProfileIDs that looks like this...

Array

(

    [3] => 3

    [4] => 4

    [8] => 8

)

 

I have a table that looks like this...

ProfileID | Interest

 

Every ProfileID could have multiple Interests, so multiple rows...

 

What I need to do is query that table that searches for those ProfileIDs as well as second set of Array Values, called Interests, and some how end up with only those IDs that ONLY have ALL of the second set, the Interests, of Array Values...

 

So here's an example of kind of where I'm at right now...

 

$firstProfileID_Array[] = '';
foreach ($RemainingProfiles_Array as $RemainingProfile_Member) {
    foreach ($Interest_Array as $Interest_Element) {
        $query = mysql_query("SELECT * FROM Interests WHERE ProfileID = '$RemainingProfile_Member' AND Interest = '$Interest_Element'");
        while ($row = mysql_fetch_assoc($query)) {
            $firstProfileID_Array[$row['ProfileID']] = $row['ProfileID'];
        }
    }
}

 

But what that leaves me with, as I'm sure you can imagine, is a list of all ProfileIDs that contain AT LEAST ONE of the Interests...

[requinix]

Here's what the SQL could look like. Execute this once to get all the matching profile IDs.

SELECT ProfileID FROM table WHERE ProfileID IN (3, 4,  AND Interest IN (9, 8, 7, 6) GROUP BY ProfileID HAVING COUNT(1) = 4

(That 4 is the number of interests.)

 

This assumes the table has no duplicate rows.

[Hobbyist_PHPer]

Here's what the SQL could look like. Execute this once to get all the matching profile IDs.

SELECT ProfileID FROM table WHERE ProfileID IN (3, 4,  AND Interest IN (9, 8, 7, 6) GROUP BY ProfileID HAVING COUNT(1) = 4

(That 4 is the number of interests.)

 

This assumes the table has no duplicate rows.

 

So how would I get the array values listed out like that in the array, in the query? (3, 4, & (9, 8, 7, 6)

The count I figure I can do sizeof() function to get that...

 

[DavidAM]

If you mean how do you get them into the query from PHP (I'm assuming they are integers)

$sql = "SELECT ProfileID FROM table
WHERE ProfileID IN (" . implode(",", $profile_id_array) . ")
AND Interest IN (" . implode(",", $interest_array) . ")
GROUP BY ProfileID HAVING COUNT(*) = " . count($interest_array);

 

If you mean, how do you get them as a column returned by the query ...

$sql = "SELECT ProfileID, GROUP_CONCAT(Interest SEPARATOR ',') AS Interests FROM table
WHERE ProfileID IN (" . implode(",", $profile_id_array) . ")
AND Interest IN (" . implode(",", $interest_array) . ")
GROUP BY ProfileID HAVING COUNT(*) = " . count($interest_array);

I guess that second statement is redundant since they will be the same as provided in the query.

[Hobbyist_PHPer]

If you mean how do you get them into the query from PHP (I'm assuming they are integers)

$sql = "SELECT ProfileID FROM table
WHERE ProfileID IN (" . implode(",", $profile_id_array) . ")
AND Interest IN (" . implode(",", $interest_array) . ")
GROUP BY ProfileID HAVING COUNT(*) = " . count($interest_array);

 

If you mean, how do you get them as a column returned by the query ...

$sql = "SELECT ProfileID, GROUP_CONCAT(Interest SEPARATOR ',') AS Interests FROM table
WHERE ProfileID IN (" . implode(",", $profile_id_array) . ")
AND Interest IN (" . implode(",", $interest_array) . ")
GROUP BY ProfileID HAVING COUNT(*) = " . count($interest_array);

I guess that second statement is redundant since they will be the same as provided in the query.

 

The Interests array are not integers, will that not work then?

[DavidAM]

Then we have to wrap them in quotes:

 

$sql = "SELECT ProfileID, GROUP_CONCAT(Interest SEPARATOR ',') AS Interests FROM table
WHERE ProfileID IN (" . implode(",", $profile_id_array) . ")
AND Interest IN ('" . implode("', '", $interest_array) . "')
GROUP BY ProfileID HAVING COUNT(*) = " . count($interest_array);

[Hobbyist_PHPer]

It worked! Thank you so very much, I really appreciate your help, requinix and DavidAM...