Facebook Cookbook

Birthday Friends

Problem

I need to find all of a specific user’s friends whose birthdays are on a given day using FQL.

Solution

SELECT uid FROM user WHERE strpos(birthday, "September 27")
 = 0 AND uid IN (SELECT uid2 FROM friend WHERE uid1 = $uid)

Discussion

Rather than supporting the LIKE comparator from SQL, Facebook has included the ability to run a small set of PHP-like functions in your queries (see Functions and Operators). We’re using the strpos() function here, which returns the position of the needle in the haystack (the first parameter—birthday—is known as the haystack, and the second parameter—“September 27”—is known as the needle). Since we’re looking for people who share the same birthday and we don’t care about the year, we want to return results where the string starts with “September 27”, which would mean it’s at position 0. In more traditional SQL, this query would have:

SELECT uid FROM user WHERE birthday LIKE "September 27%"
 AND uid IN (SELECT uid2 FROM friend WHERE uid1 = $uid)

You can, of course, substitute any day you’d like for “September 27”, which just happens to be my birthday (please email cakes and other goodies). This query comes from the excellent Facebook Developers Wiki Sample FQL Queries page, which you’ll find at http://wiki.developers.facebook.com/index.php/Sample_FQL_Queries.