Search.inc: Running multiple searches on a single page

Note that db_table_exists() will not work in MySQL on temporary tables.

http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html

The SHOW TABLES statement does not list TEMPORARY tables.

This is true in MySQL < 5 as well.

http://dev.mysql.com/doc/refman/4.1/en/temporary-table-problems.html

The attached seems to fix this problem.

One additional issue was that searches that matched no results will not create the temp_search_results table, so we can't blanket delete the table. We also can't check to see if it exists (see above).

We can, however, force behavior that tells us if those tables exists.

This solution works by incrementing a global variable to count successful searches.

global $mysite_search_loops

When a search returns results, we increment the var:

    // since we are searching nodes, better to use node_search instead of do_search
    $result = node_search('search', $keywords);
    // if matches, then increment the counter so we can wipe the tables
    if (count($result) > 0) {
      $mysite_search_loops++;
    }

Then, before we begin the next search, we run a wipe check for the two known temporary tables.

    // search.module creates temporary tables, we must explicitly DROP them 
    // to perform more than one search on a single page view.
    if ($mysite_search_loops > 0) {
      if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_results'))) > 0) {
        db_query_temporary("DROP table temp_search_results");    
      }
      if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_sids'))) > 0) {
        db_query_temporary("DROP table temp_search_sids");    
      }
    }

Apparently, the DROP works with temporary tables. In my test install (MySQL 4.1.x, I get no errors).

Anyone able to test on PGSQL?

Only the first IF statement is necessary here.

if ($mysite_search_loops > 0) {
if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_results'))) > 0) {
db_query_temporary("DROP table temp_search_results");
}
if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_sids'))) > 0) {
db_query_temporary("DROP table temp_search_sids");
}
}

By default, the $mysite_search_loops var is only > 1 if the other two conditions are TRUE.

Attached is the fix.

I'd love to see it. Not sure I understand the last comment, though.

You could, in theory, use MySite to save the faceted search if the other module doesn't support saving searches. Take a look at the 'refine.inc' file in the contrib folder of the download.

Using the {mysite_content} table and the function mysite_get_myid(), you can store the user's search inside the MySite module.

Sure, I'd love to see it in action.

I was merely saying that if the faceted search author doesn't want to save searches, you have the option of using internal MySite functions to do so. In fact, that's what search.inc does.

Well, Drupal 6 has fixed this issue (temporary tables), so after the port to D6, we can probably revive this feature.