Drupal: Custom profile field search page
Last week I got a request to build a search page where a paying member of a site could search profile fields of other registered members. The community is built on Drupal 5.0 and I thought it would be a relatively easy task of integrating a finished module enabling search on profile fields. The customer wanted the search result to be presented by one row with three columns each holding a thumbnail image of the member who matched the search criteria.
I was slightly wrong because I could not find a good module to download from www.drupal.org. So I decided to analyze the database to see how the data was structured and to see if it was possible to create a custom PHP snippet that would provide a visitor a page where he makes a detailed search of members of a site.
The first thing I did was to see if I could run MySQL queries straight from a node in Drupal. I made a test node and pasted a short MySQL query embedded in PHP printing out the name of all the users registered from the database and I was happy to get a short list of all four users currently signed up on my test site.
select name from users
This was a really simple query.
Then I had to find out how Drupal stores profile fields. There are two tables of importance here, first “profile_fields” which holds the name and ID of all profile fields. The second field is the “profile_values” which holds all data from the fields in the profile that users have submitted.
Data in the “profile_values” table is stored in the “value” column, the column “fid” points to the ID of the field. The column “uid” points to the ID of the user in the “users” table.
So it was fairly clear to see the relationship between all the tables, however making an actual search form seemed to be a bit more advanced. I know that you could probably run some really fancy left, right and upper join to get the search info, but I have never been that good at complicated joins in SQL and the clock was ticking.
Programming is breaking down a problem into many smaller problems and solving them one after another.
The first part was making a small standard search form that listed two fields from the “profile_values” table in two separate drop down lists. This turned out to be a no brainer. I added a new column to the “profile_values” table called “search”, then I flagged all the rows holding interesting profile values to be searched with a “1″, for example age and hair color. In order to fill the drop down list with options to be chosen you need to use the PHP function “explode” on the data that is stored in the “options” column in “profile_fields”.
Anyone working with dynamic websites such as Drupal should be able to know how to pass values around pages, so if one option in a drop down list is “30″ it is fairly easy to do a query in the database on the “profile_values” table for all rows that hold “30″ for the “fid” of 4 (in this example the profile field “Age” has a “fid” of 4).
select * from profile_values where value=’30′
That is still basic query stuff over there. Nothing advanced at all.
So when I run this query I also stuff the output into an array. Say a user wants to search for all users who are 30 years old and who lives in Stockholm. That would produce two queries, one like above, and the next almost the same “value=’Stockholm’. If you stuff the output from these queries into two different arrays you can then compare these and find similarities. The output you want to input into the array is the “uid” from the “profile_values” table where a match has occurred.
In the example above I named one array $uid_age and the other $uid_city.
Alright, we now have two arrays containing the matches, the first array contains every member ID that is 30 years old, and the second array holds every member ID who lives in Stockholm.
We are interested in the members who are both 30 years old and lives in Stockholm so we need to compare both arrays to find the “UID”, member ID:s, that are the same in both arrays.
The PHP function array_intersect does that nicely.
$result = (array_interserc($uid_age,$uid_city))
The above PHP snippet gives us an array called $result that holds the matches of member ID:s from both arrays who are 30 years old and who lives in Stockholm.
With this data in an array it is possible to further make a query that fetches the thumbnail image of the user and prints it out row by row column by column.
Hopefully someone will make a good profile field searcher module for Drupal in the future, until then, you will need to create your own. I hope you got inspiration and guidance from this short article.