By ffletch on

I need to create a form that has a text field that uses autocomplete. The data used for the autocomplete text would live in a database. However the database would not be the drupal mysql database - it would be another database (possibly even another database server and type - another server running MS SQL Server for example). I like the way that http://dev.vocalvoter.com as it appears to use the autocomplete functionality from core.

Does anyone know of a way to use the autocomplete functionality in core (assume 5.x) but have it pull its data from another database?

⋅ Categories: Drupal 5.x

Comments

pwolanin’s picture

pwolanin commented

see: http://api.drupal.org/api/5/file/developer/topics/forms_api_reference.ht...

I'm not sure whether you can put an external path in - you'll have to work through the code, but it seems likely: http://api.drupal.org/api/5/function/theme_textfield

Look at the existing autocomplete functions to understand what the callback needs to return. e.g.: http://api.drupal.org/api/5/function/user_autocomplete

---
Work: BioRAFT

kbahey’s picture

kbahey commented

In settings.php

$db_url['default'] = 'mysql://user:pass@localhost/drupal';
$db_url['yourdbname'] = 'mysql://user:pass@localhost/yourdbname';

Then in your module you do:

function something_autocomplete($string = '') {
  $matches = array();
  if ($string) {
    if (db_set_active('yourdbname')) {
      $result = db_query_range("SELECT foo FROM {bar} WHERE baz LIKE '%s%%'", $string, 0, 10);
      while ($row = db_fetch_object($result)) {
        $matches[$row->foo] = check_plain($row->foo);
    }
    print drupal_to_js($matches);
    exit();
    }
  }
}

--
Drupal development and customization: 2bits.com
Personal: Baheyeldin.com

--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba

ffletch’s picture

ffletch commented

How do I call this from a form? I see how to make a text field use autocomplete but its not clear how to tell a textfield to use this function instead. Or should I not be using the forms API?

ffletch’s picture

ffletch commented

Now that I have spent some late nights messing around with this, I realize what a huge question I was asking. So for the benefit of others, here are some comments:
- I did have to add a: db_set_active('default'); to stop some errors in my watchdog log. I think it was trying to query some tables that are in the drupal db out of the db that I specified.
- Basically what doing an autocomplete comes down to is you must provide #autocomplete_path' => '<url>' where <url> returns something in this style: { "string1": "string1", "string2": "string2", "string3": "string3" }. The code provided above makes drupal spit out a page like that.
- The db_set_active "trick" / adding more db's to settings.php does not work for MSSQL (yet - as I've read). So I ended up writing my own page that returns a query from an MSSQL server in the format specified above and it works as an autocomplete path.
- It might also be worth noting that in the format specified above the first "string1" corresponds to what is entered in the textfield after clicking or arrowing down and the second "string1" appears to be what actually appears in the autocomplete "pull-down". Knowing this I was able to easily add code to highlight what is is that you searched for.

Most of the above I found by experimenting and playing around. Some of it I was able to find in documentation somewhere (e.g. MSSQL is not supported). The stuff I figured out on my own may be documented somewhere but I couldn't find it (possibly from a lack of good "deep" searching?). If anyone can point me in a direction that might confirm some of my finding, that would be a great source of reference for me an I would appreciate it. On that note - some of this might be flat out wrong. If that is the case - please correct where appropriate.