[RESOLVED] Find the mode in list of numbers

[digital_skwerl]

I'm doing some matching of user profiles and have gotten this far.
First select the associations related to the user's account.
Second select all the other user id's who also have those associations in common.
Third create a list (array) of user id's of all users who have matching criteria.

Now I want to select from that list the user id's that appear most often (meaning that they have the most associations in common with the user)

Mathematically this would be the MODE of the set of numbers. I have searched the manual and cannot find a MODE function.

How can this be accomplished?

Here is some code:

	if (isset($_SESSION['user_id'])) { 
		require_once ('./x/mysql_connect.php');
		$uid = $_SESSION['user_id'];
		$querya = "SELECT association FROM uassociations WHERE user_id='$uid'";
		$resulta = @mysql_query($querya);
		$checka = @mysql_num_rows($resulta);
		if ($checka > 0) {
			$list = '';
			while ($rowa = @mysql_fetch_array($resulta, MYSQL_NUM)) {
				$assoc = $rowa[0];
				$query1a = "SELECT user_id FROM uassociations WHERE association='$assoc' AND user_id NOT LIKE '$uid'";
				$result1a = @mysql_query($query1a);
				$check1a = @mysql_num_rows($result1a);
				if ($check1a > 0) {
					while($row1a = @mysql_fetch_array($result1a, MYSQL_NUM)) {
						$list = "$row1a[0], $list";
					}
				}
			}
		}
		$array = explode(',', $list);

[laserlight]

Well, in PHP you could make use of [man]array_count_values/man, but I suspect that there may be a way to do it from the database backend. What is your relevant database schema?

[digital_skwerl]

SET FOREIGN_KEY_CHECKS=0;
#----------------------------

Table structure for uassociations

#----------------------------
CREATE TABLE uassociations (
uassociation_id int(5) NOT NULL auto_increment,
user_id int(8) NOT NULL default '0',
association varchar(255) NOT NULL default '',
association_date date NOT NULL default '0000-00-00',
PRIMARY KEY (uassociation_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#----------------------------

Records for table uassociations

#----------------------------

insert into uassociations values
(7, 26, 'BNI', '2007-04-19'),
(8, 34, 'BNI', '2007-04-19'),
(9, 34, 'Crazy People', '2007-04-19'),
(10, 34, 'Networker Inc.', '2007-04-19'),
(11, 34, 'Womens Group', '2007-04-19'),
(12, 26, 'Networker Inc.', '2007-04-20');

[digital_skwerl]

Laserlight,
Let's say for example I want to pull the 10 user_id's with the most associations in common with the user. If I was to use array_count_values, how can I order by the count DESC and limit to 10?

Starting to think I might be able to use a join and do this all in one query but not sure where to begin. Any help?

[laserlight]

If you are not able to use subqueries, this may be a possible solution:

$uid = (int)$_SESSION['user_id'];

// Retrieve associations (and number of associations) with this user.
$query = "SELECT association, COUNT(association) AS association_count
          FROM uassociations WHERE user_id=$uid GROUP BY association";
$result = mysql_query($query);
$associations = array();
while ($row = mysql_fetch_assoc($result)) {
    $associations[$row['association']] = $row['association_count'];
}

if (count($associations) > 0) {
    // Find association with highest count.
    $max = max($associations);
    $max_association = array_search($max, $associations);

// Select user with highest association level with this user.
$query = "SELECT user_id FROM uassociations
          WHERE association=$max_association";
$result = mysql_query($query);
// ...
}

My idea is to use one query to find the list of associations with the user, and the number of times these associations appear. With that, you can find the (or a) maximum, and then select the associated user with another query. If you need to find the users with the top 10 number of associations, then you have to modify this second query to use a "WHERE association IN (...)".

As a side note: I think some kind of MAX(COUNT(association)) could come into play, so perhaps a database expert can demonstrate an even better way.

[laserlight]

Silly me, I just realised that you can sort and take the first n associations.

$uid = (int)$_SESSION['user_id'];

// Retrieve associations (and number of associations) with this user.
$query = "SELECT association, COUNT(association) AS association_count
          FROM uassociations WHERE user_id=$uid
          GROUP BY association ORDER BY association_count DESC LIMIT 10";
$result = mysql_query($query);
$associations = array();
while ($row = mysql_fetch_assoc($result)) {
    $associations[] = $row['association'];
}

if (count($associations) > 0) {
    // Select users most associated with this user.
    $query = "SELECT user_id FROM uassociations
              WHERE association IN (" . implode(',', $associations) . ")";
    $result = mysql_query($query);
    // ...
}

[digital_skwerl]

Thank you laserlight, this looks good and you are awesome. I will try this out and get back to this post with my results. 🙂

[Roger_Ramjet]

What are the associations? Are they a standard set as appears from your test data? If so then you should have a table that lists them and store the id in the uassoc table. Not essential but usual way to store this type of data. Makes the table more compact and queries faster to run when using integer indexes instead of text.

Laserlight, I don't see how your query is going to achieve anything. ??? Although there is no indexing to ensure that a single user does not have the same association entered more than once, I guess that steps are taken elsewhere to ensure this does not happen.
All your first query is going to do is to return the assocs for user==$uid each with a count of 1, yes.
The second query will return multiple rows for users with multiple common assocs to the first user, and I think this is where you meant to use the count.

Digital, you can do this in one query:
standard sql syntax with subquery

"SELECT uid, COUNT(uid) AS count FROM
(SELECT  t2.user_id AS uid FROM uassociations t1 INNER JOIN uassociations t2
 ON t1.associations=t2.associations WHERE t1.user_id=$uid)
GROUP BY uid
ORDER BY count DESC
LIMIT 10"

You could also get away without the subquery by using mysql's sloppy and dodgy GROUP BY syntax, which allows you to select columns not mentioned in the group by clause. The results are unpredictable when you do that so I don't recommend it.

[laserlight]

Laserlight, I don't see how your query is going to achieve anything. ??? Although there is no indexing to ensure that a single user does not have the same association entered more than once, I guess that steps are taken elsewhere to ensure this does not happen.

The looks of it is that digital_skwerl's database is not normalised, thus there are duplicate associations with different dates. I can see no other way by which he/she would be attempting his/her original code with that schema.

[Roger_Ramjet]

Ah well, I was only looking at the test data.

Should have guessed since the rest of it is not normalised anyway. I suppose there is one of those 'utterly necessary' reasons for associations being repeated, but I can't imagine what.

Digital, normalise your data first.

[digital_skwerl]

What are the associations? Are they a standard set as appears from your test data? If so then you should have a table that lists them and store the id in the uassoc table.

I do have the associations stored in another table with id's and I will begin using the id's instead of the assoc name.

Although there is no indexing to ensure that a single user does not have the same association entered more than once

Duplicate entries are handled elsewhere.

"SELECT uid, COUNT(uid) AS count FROM 
(SELECT  t2.user_id AS uid FROM uassociations t1 INNER JOIN uassociations t2 
ON t1.associations=t2.associations WHERE t1.user_id=$uid) 
GROUP BY uid 
ORDER BY count DESC 
LIMIT 10" 

Why don't I get INNER JOINS? I'll work with this a bit.

Thanks Roger and Laserlight

[Roger_Ramjet]

Whoops syntax error

"SELECT uid, COUNT(uid) AS count FROM
(SELECT t2.user_id AS uid FROM uassociations t1 INNER JOIN uassociations t2
ON t1.associations=t2.associations WHERE t1.user_id=$uid) AS t
GROUP BY uid
ORDER BY count DESC
LIMIT 10"

[digital_skwerl]

Using array_count_values I can create a new array with the user id and the frequency.

$array = explode(',', $list);
		print_r(array_count_values($array));

Returns: Array ( [35] => 1 [34] => 2 [0] => 1 )

Can't I now just sort this array and loop through the results stopping after 10?

If this is a good way to do it, what can I use to sort this array?
Looking at usort and array_multisort but am unfamiliar.

[Roger_Ramjet]

No it is not a good way to do it. SQL and relational databases are designed and optimised for this kind of operation and sql is 10-20 times faster than code because of this.

INNER JOINS are easy; they correspond exactly to the Intersection of Sets.

Relational databases are based on Set Theory and Propositional Logic. Given that you seem to have a mathematics background you should find them easy.

[digital_skwerl]

Ok I understand. I have achieved the desired result using your query and inner join. Seems to be working perfectly, and with much less code than my original attempt. Thanks for the help Roger, looks like I need some review in subqueries/joins.

Here is the final code for anyone interested.

if (isset($_SESSION['user_id'])) { 
		$uid = $_SESSION['user_id'];
		require_once ('./x/mysql_connect.php');
		$query1a = "SELECT uid, COUNT(uid) AS count FROM 
		(SELECT t2.user_id AS uid FROM uassociations t1 INNER JOIN uassociations t2 
		ON t1.association=t2.association WHERE t1.user_id=$uid) AS t
		GROUP BY uid 
		ORDER BY count DESC 
		LIMIT 10";
		$result1a = @mysql_query($query1a);
		$check1a = @mysql_num_rows($result1a);
		if ($check1a > 0) {
			echo "<table width=100% cellspacing=3 cellpadding=2 class=dtacnt1>";
			while($row1a = @mysql_fetch_array($result1a, MYSQL_ASSOC)) {
				$user = $row1a['uid'];
				if ($user == $uid) {
					} else {
					$query1 = "SELECT first_name, last_name, city, state, thumb FROM users WHERE user_id='$user'";
					$result1 = @mysql_query($query1);
					if ($result1) {
						$row1 = @mysql_fetch_array($result1, MYSQL_NUM);
						echo "<tr><td><a href=profile_view.php?uid=$user><img src=$row1[4] class=img1></a></td>
						<td width =100%><a href=profile_view.php?uid=$user>$row1[0] $row1[1]</a><br>$row1[2], $row1[3]</td></tr>";
					}
				}
			}
			echo "</table>";
			} else {
			echo "<table width=100% cellspacing=0 cellpadding=5 class=infotxt1>
			<tr><td width =100%><img src=images/arrow_blue_2.png> There are no matching profiles available.</td></tr></table>";
		}
	}

[Roger_Ramjet]

Usual error - running a query for each result when all data could be extracted at once in one query.

"SELECT t.uid, COUNT(t.uid) AS count, u.first_name, u.last_name, u.city, u.state, u.thumb
        FROM
        (SELECT t2.user_id AS uid FROM uassociations t1 INNER JOIN uassociations t2
        ON t1.association=t2.association WHERE t1.user_id=$uid AND t2.user_id<>$uid) AS t 
        INNER JOIN users u ON t.uid=u.user_id
        GROUP BY t.uid, u.first_name, u.last_name, u.city, u.state, u.thumb
        ORDER BY count DESC
        LIMIT 10"; 

I used standard sql syntax for the first query so that it could be extended easily and still work across most databases. For the same reason I have extended it with standard sql syntax rather than the sloppy crap you can get away with in mysql.

[Roger_Ramjet]

PS, modified the subquery to exclude self-matches so you don't have to filter them in code. Forgot about that before, sorry.