Select record with most similar fields

[sdotsen]

Trying to print out the user with the most similarity with another user.

 

Based on User A's preferences find the user with the most similar taste and print them in order, but do not print user with nothing in common.

 

User A likes blue, green, red, orange, purple

 

--

 

User B likes blue, green

User C likes orange, purple

User D likes green, yellow, gold

User E likes orange, blue, green, silver

User F likes yellow, cyan, gold

 

Based on those, I would like it to print as follows (in order).

 

User E (3 similar)

User B (2 similar)

User C (2 similar)

User D (1 similar)

 

DO NOT PRINT User F since that user has no similar taste.

 

* Profiles list all users' profiles with the primary key being profile_id

* Traits are a list of traits (think tagging) input by users with trait_id as the primary key

* Matches contain 2 fields (profile_id and trait_id).

 

So, essentially if bettysue (profile_id = 200) tags herself with "nerdy" (trait_id = 10) in the matches table there would be a row with profile_id = 200 and trait_id = 10.

 

A trait can have more than one profile just as a profile can have more than 1 trait assign to them. Makes sense?

 

 

 

Any tips? I think a for loop is needed along with an array but getting it started is difficult for me.

 

 

[Barand]

Use array_intersect() to get common values and count the resulting array

 

<?php
$A = array ('blue', 'green', 'red', 'orange', 'purple');

$others = array (
    'B' => array ('blue', 'green'),
    'C' => array ('orange', 'purple'),
    'D' => array ('green', 'yellow', 'gold'),
    'E' => array ('orange', 'blue', 'green', 'silver'),
    'F' => array ('yellow', 'cyan', 'gold' )
);
$results = array();

foreach ($others as $k => $prefs) {
    $similar = array_intersect($A, $prefs);
    $results[$k] = count($similar);
}
arsort ($results);

foreach ($results as $k => $n) {
    if ($n > 0)
        echo "User $k ($n similar)<br>";
}
?>
[[code]

[/code]

[sdotsen]

Barand ... that's awesome ... now I just gotta figure out how to do it with SQL statements since I'm pulling the results from a DB. Thanks for the tip.

[sasa]

$user = 200;
$sql = "SELECT profile_id, COUNT( trait_id ) AS c
FROM matches
WHERE trait_id IN (SELECT trait_id FROM matches WHERE profile_id =$user) AND profile_id <>$user
GROUP BY profile_id ORDER BY c DESC";

[Psycho]

You just need a single, somewhat complex query - you never want to have looping with queries if you can help it. Since I don't know the actual names of your tables and columns I just guessed. You should be able to figure it out:

 

<?php

$userID; //the id of the usre to find matches against

$sql = "SELECT p1.profile_name, COUNT(p1.profile_id) as trait_count
       FROM profiles p1, traits t1
       WHERE p1.profile_id = t1.profile_id

         AND p1.profile_id <> '$userID'

         AND t1.trait_id IN (
           SELECT t2.trait_id
           FROM traits t2
           WHERE t2.profile_id = '$userID'
         )

       GROUP BY p1.profile_name
       ORDER BY trait_count DESC";

?>