Query Help

[gawdz]

Let me give you some insight im doing this to look for compatibility between users.

 

 

This is where i gather all the "ratings" the logged in user did.

 

$myself = "SELECT storyId,username FROM ratings WHERE username<>'Anonymous' AND username='".$session->username."'";
$result = mysql_query($myself);
while ($row = mysql_fetch_array($result)) {
$ratings[] = $row['storyId'];
}

 

Now im going to go through all the users and see how many times they did the exact same thing my logged in user did.

		$sql = "SELECT username FROM users";
		$result = mysql_query($sql);
			while ($row = mysql_fetch_array($result)) {
				foreach ($ratings as $value) {
				$compat = "SELECT id,username FROM ratings WHERE id='".$value."' AND username<>'Anonymous' AND username<>'".$session->username."' AND username='".$row['username']."'";
					$result2 = mysql_query($compat);
						while ($row2 = mysql_fetch_array($result2)) {
							$compatibles[] = $row2['username'];
								}
						}

					}

 

But the thing is it times out because i poorly made the query but im not sure how to improve it.

[s0c0]

If I understand what you are looking to accomplish here is some pseudo code that will help.  Avoid looping through a result set and issueing a query each time.  Instead utilize the IN and NOT IN operators in this case. 

 

$q1 = "SELECT storyId FROM ratings WHERE username='".$session->username."';
// store q1 in an array lets call that $storyArr
$q2 = "SELECT username FROM ratings WHERE username NOT  IN ('$session->username','Anonymous') AND storyId IN (".implode(',',$storyArr).")";
// $q2 is what you need and will have a list of compatible users...

 

 

 

 

[DavidAM]

As s0c0 said, you should combine queries whenever possible. You might want to add DISTINCT to the final query in his solution though, otherwise, you will get the same user multiple times (if they have rated more than one story in the list). 

$q2 = "SELECT DISTINCT username FROM ratings 
WHERE username NOT  IN ('$session->username', 'Anonymous') 
AND storyId IN (".implode(',',$storyArr).")";

 

If you are looking for people who have rated ALL (not just ANY) of the same stories as the user, I think this will do it

$q2 = "SELECT username FROM ratings 
WHERE username NOT  IN ('$session->username', 'Anonymous') 
AND storyId IN (".implode(',',$storyArr).")
GROUP BY username HAVING COUNT(*) = " . count($storyArr);

 

Also, just a comment on your original query. It is really NOT necessary to say WHERE username IS NOT Anonymous when you are also saying WHERE username IS (current user). Since username can only be one value, if it IS (current user) is IS NOT anonymous -- unless of course the current user IS anonymous in which case your original query will return no rows.

 

$myself = "SELECT storyId,username FROM ratings 
WHERE username<>'Anonymous' AND username='".$session->username."'";