MySQL help...

[DragonMistress]

Hi, I have a multi-select field with 30 values that is being saved with || as the separators between the values for each userid in my database. I've been trying to determine for days now how to pull the data from this field and compare each users data to determine everyone that matches more than 20 of each others values.

 

Let's see if I can explain further (in smaller amounts)....

 

Let's say I have 4 users and each one has 5 values in one field.

 

User1 - 1 || 2 || 3 || 4 || 5

User2 - 2 || 3 || 4 || 6 || 7

User3 - 3 || 4 || 7 || 8 || 9

User4 - 2 || 3 || 4 || 5 || 10

 

Ok now I want to pull those values from the database and determine which of those four users match 3-5 of those values.

 

So the result for User1 would be:

User2 - Matches 3 out of 5

User4 - Matches 4 out of 5

 

Now perhaps this can't be done strictly with a MySQL query? It will be going into a PHP script, can it be done with PHP? Or would I need some javascript to accomplish this?

[Maq]

*Not tested.  This code will probably contain errors, just post them and I will fix it.

 

Assuming there are always 5 values separated by " || " in my example and there is a field called "userid" (auto-increment) and a field called "values" which is a string.

 

ini_set ("display_errors", "1");
error_reporting(E_ALL);

$get_all = "SELECT * FROM table WHERE userid = 1";
$result = mysql_query($get_all);
$row = mysql_fetch_array($result);
$all_nums = explode(" || ", $row['values']);

$get_matches = "SELECT * FROM table WHERE user  {$row['userid']}";
$result2 = mysql_query($get_matches);
while($row2 = mysql_fetch_array($result2)) {
$matches=0;
$comp_arr = explode(" || ", $row2['values']);
   foreach($all_nums as $key => $value)
   {
	if(in_array($value, $comp_arr))
	{
		$matches++;
	}
}
echo $row['userid'] . " - Matches " . $matches . " out of 5";
}

?>

[fenway]

5 values in one field? You should normalize ASAP!