Specifications

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 set-
ting 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 con-
tains 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 cer-
tain 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 aver-
age Web page.
Building User Authentication and Personalization
C
HAPTER 24
24
AUTHENTICATION
AND
PERSONALIZATION
533
30 7842 ch24 3/6/01 3:34 PM Page 533