By rschwab on

Hi Drupalers!

I'm weighing my options for how to accomplish a custom search page for my drupal site, and would very much appreciate some advice from more experienced developers. Specifically, I want it to look pretty much identical to this search form:
http://www.adoptionhelp.org/adoptive_families/searchall_new.html

So it seems like I have three options here:
1. Faceted Search using religion, ethnicity etc taxonomies
2. Apache Solr search (I've no idea how this might be done)
3. Custom php to query the database and return the results.

Using faceted search, I'm not sure I could get the same fine grained results that are accomplished on the search page above. For example, can a user make selections from multiple taxonomies using faceted search?

If I went with option 3, can anyone give advice as to how to display the results in a Views display?

Thanks in advance for any help! I've been looking at all these options a lot, but don't want to invest a lot of time pursuing one without some outside opinions.

- Ryan

⋅ Categories: Drupal 6.x

Comments

rschwab’s picture

rschwab commented

No drupal ninja's around today?

Edit: How about today? :)

rschwab’s picture

rschwab commented

Well I've begun work on a custom module to accomplish this, and I've hit a bit of a snag and would love anyones opinion. (To understand what I'm talking about, just take a quick look at the link above... this is the search beast I need to give life to).

Because the way drupal sets up the profile_values fields so that each answer has its own row, I'm starting to think I have two options for getting this search done:

1) A complicated series of SQL queries that will get me arrays of uids of profiles that match. For example. I'd have to run one query for religion options, and then compare the results against the results of the region and language queries. This seems like a pain to code, and heavy on the database because each search would include a different query for every profile field the person specified in their search.

2) A second module to basically take the profile_values table and re-create it with each user being in 1 row. Then each search would be one query where all the values had to match, and I'd get one nice array of user ids back of those who matched the search terms.

It seems like option 2 is a better plan, but before I dive in I'm hoping to get a more experienced developers opinion.

Thanks in advance for your time!
- Ryan

rschwab’s picture

rschwab commented

Learn SQL Noob! I wish I had learned about INNER JOINs before I bothered with this. Oh well, I finished #2 and it works, albeit its not good DB design. C'est la vie.

I'm now working on the 2nd half of this problem: integrating a keyword search into the existing search. I've looked through search.module and also the Profile Plus module, and I'm wondering how bad it would be to just copy some of the string sanitizing functions and just run straight SQL queries on the keywords?

Or, even better yet, does anyone know a way to integrate a keyword search into a custom search module like the one here: http://www.iheartadoption.org/find-a-family

The end goal is to be able to limit searches by both keyword and the other search criteria in the form. From what I can tell this isn't possible unless I build another custom module. Am I right about this?

Thanks,
- Ryan

rschwab’s picture

rschwab commented

That wasn't so bad. Here is the code that makes this work:

<?php
	// Process keyword search string
	if (!empty($searchstring)) {
	$keywords = explode(' ', check_plain(strip_tags($searchstring)));
    $i = count($keywords);
    $keys = preg_replace('!\*+!', '%', $keys);

	// If there are keywords, step through them
    if ($i > 0) {
      $match = array();
      while ($i > 0) {
        $j = $i - 1;
	
	    // Query the database for matches to this keyword
        $sql = "FROM {profile_values} pv INNER JOIN {profile_fields} pf ON pv.fid = pf.fid WHERE pv.value LIKE '%%%s%%' AND pf.visibility IN (%d, %d)";
        $result = db_query('SELECT DISTINCT pv.uid '. $sql, $keywords[$j], PROFILE_PUBLIC, PROFILE_PUBLIC_LISTINGS);
		
		// Add distinct matches to the $match array
		while ($auser = db_fetch_object($result)) {        
		   if (!in_array($auser->uid, $match)) {
        		$match[] = $auser->uid;
           }
		}
		
		// One less keyword to go
        $i = $i - 1;
		
      } // End while statement
	} // End if statement - gone through all keywords
	
	// Build SQL segment
	$query_keyword = '';
	foreach ($match as $key => $value) {
		if (empty($query_keyword)) {	$query_keyword = " AND (uid = '$value'"; }
		else { $query_keyword .= " OR uid = '$value'"; }
	}
	$query_keyword .= ')';
	
	} // end If statement at top of keyword processing
?>

Thanks to the authors of the core Search module, and Profile Plus. Without their code to lift and modify, this would've taken waaaaay longer.

- Ryan

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented

I see you've basically fixed this, but for others I suggest looking into http://drupal.org/node/322707 which has the goal of providing SOLR search for user profile data.

--
Morris Animal Foundation