Implementing Recommendations

Finally, we come to the link recommender script, recommend.php.

There are many different ways we could approach recommendations. We have decided to perform what we call a "like-minds" recommendation. That is, we will look for other users who have at least one bookmark the same as our given user. The other bookmarks of those other users might appeal to our given user as well.

The easiest way to implement this as an SQL query would be to use a subquery. First, we get the list of similar users in a subquery, and then we look at their bookmarks in an outer query.

However, as you might recall, MySQL does not support subqueries. We will have to perform two different queries and feed the output of the first into the next. We can do this either by setting up a temporary table with the results from the first query, or by processing the first query results through PHP.

We have chosen the second approach. Both approaches have merit. Using a temporary table is probably slightly faster, but processing in PHP makes it easier to test and modify the code.

We begin by running the following query:

					
select distinct(b2.username)
from bookmark b1, bookmark b2
where b1.username='$valid_user'
and b1.username != b2.username
and b1.bm_URL = b2.bm_URL

				

This query uses aliases to join the database table bookmark to itself—a strange but sometimes useful concept. Imagine that there are actually two bookmark tables, one called b1 and one called b2. In b1, we look at the current user and his bookmarks. In the other table, we look at the bookmarks of all the other users. We are looking for other users (b2.username) who have an URL the same as the current user (b1.bm_URL = b2.bm_URL) and are not the current user (b1.username != b2.username).

This query will give us a list of like-minded people to our current user. Armed with this list, we can search for their other bookmarks with the following query:

					
select bm_URL
from bookmark
where username in $sim_users
and bm_URL not in $user_urls
group by bm_URL
having count(bm_URL)>$popularity

				

The variable $sim_users contains the list of like-minded users. The $user_urls variable contains the list of the current user's bookmarks—if b1 already has a bookmark, there's no point in recommending it to him. Finally, we add some filtering with the $popularity variable—we don't want to recommend any URLs that are too personal, so we only suggest URLs that a certain number of other users have bookmarked.

If we were anticipating a lot of users using our system, we could adjust $popularity upwards to only suggest URLs have been bookmarked by a large number of users. URLs bookmarked by many people might be higher quality and certainly have more general appeal than an average Web page.

The full script for making recommendations is shown in Listing 24.26 and 24.27. The main script for making recommendations is called recommend.php (see Listing 24.26). It calls the recommender function recommend_urls() from url_fns.php (see Listing 24.27).

Listing 24.26 recommend.php—This Script Suggests Some Bookmarks That a User Might Like

<?
 require_once("bookmark_fns.php");
 session_start();
 do_html_header("Recommending URLs");
 check_valid_user();
 $urls = recommend_urls($valid_user);
 display_recommended_urls($urls);

 display_user_menu();
 do_html_footer();
?>

Listing 24.27 recommend_urls() Function from url_fns.php—This Script Works Out the Actual Recommendations

function recommend_urls($valid_user, $popularity = 1)
{
  // We will provide semi intelligent recommendations to people
  // If they have an URL in common with other users, they may like
  // other URLs that these people like
  if (!($conn = db_connect()))
    return false;

  // find other matching users
  // with an url the same as you

  if (!($result = mysql_query("
                    select distinct(b2.username)
                    from bookmark b1, bookmark b2
                    where b1.username='$valid_user'
                    and b1.username != b2.username
                    and b1.bm_URL = b2.bm_URL
                   ")))
     return false;
  if (mysql_num_rows($result)==0)
    return false;

  // create set of users with urls in common
  // for use in IN clause
  $row = mysql_fetch_object($result);
  $sim_users = "('".($row->username)."'";
  while ($row = mysql_fetch_object($result))
  {
      $sim_users .= ", '".($row->username)."'";
  }
  $sim_users .= ")";


  // create list of user urls
  // to avoid replicating ones we already know about
  if (!($result = mysql_query("
                    select bm_URL
                    from bookmark
                    where username='$valid_user'")))
    return false;

  // create set of user urls for use in IN clause
  $row = mysql_fetch_object($result);
  $user_urls = "('".($row->bm_URL)."'";
  while ($row = mysql_fetch_object($result))
  {
      $user_urls .= ", '".($row->bm_URL)."'";
  }
  $user_urls .= ")";

  // as a simple way of excluding people's private pages, and
  // increasing the chance of recommending appealing URLs, we
  // specify a minimum popularity level
  // if $popularity = 1, then more than one person must have
  // an URL before we will recommend it

  // find out max number of possible URLs
  if (!($result = mysql_query("
                    select bm_URL
                    from bookmark
                    where username in $sim_users
                    and bm_URL not in $user_urls
                    group by bm_URL
                    having count(bm_URL)>$popularity
                  ")))
    return false;
  if (!($num_urls=mysql_num_rows($result)))
    return false;

  $urls = array();
  // build an array of the relevant urls
  for ($count=0; $row = mysql_fetch_object($result); $count++)
  {
     $urls[$count] = $row->bm_URL;
  }
  return $urls; 
}

Some sample output from recommend.php is shown in Figure 24.11.

Figure 24.11. The script has recommended that this user might like pets.com. Two other users in the database who both like slashdot.org have this bookmarked.