Example PHP

There are several syntax errors here, and the array should be keyed.

The syntax errors seem to be:

1) The SQL statement is built from a malformed literal, it does not end with semi-colon, and it should wrap the table name between {} so that drupal can replace the name with the proper table prefix. Example:

$sql = "SELECT name, slug FROM {locations} WHERE slug LIKE '%%%s%%' ORDER BY slug ASC";

2) db_fetch_object() is not a member method of $result but a standalone function.

3) Not a syntax error, but seems a can of worms: The returned array should be hashed with a known key, where the key itself would be the value stored in database, and the value would be what the user sees when looking at/editing the node. Otherwise, the key that represents the values will change as soon as you insert more values to the data source.

This particular feature works as if you were building a list of values for radios/checkboxes widgets provided by CCK itself.

I was about to post an issue but found this thread, and I think my question might fit in here.

I'm wondering how to run queries based on what the user is typing. It seemed to me that at first, from the UI, that this was simply using the PHP input to create an array, and then using something like in_array() to see if the value was contained there. This would be terribly inefficient in cases where there could be a large number of results, which is why using a SELECT query based on user input would be so useful.

I see here that Gold is running a query based on arg(2). I'm guessing that this is a little bit klugey - it depends on knowing which argument is where when the autocomplete url is being called (though that's not so difficult to find out, but it might at some point change with an upgrade, and doesn't seem "stable" since it is not really "documented functionality").

I didn't find a description of how the value the user inputs can be used in doing a LIKE query. Also, ideally, upon validation, I suppose one could still use the same LIKE query ... but it would be nice if the docs would also cover validation, so the squeamish amongst us would be better able to trust this module.

Thank you so much for writing this module, now that views do not have argument handling capacity, this type of module is VERY IMPORTANT for creating autocomplete selects which are a bit "smarter" now than what's allowed with nodereference, user reference, and views without any argument handling. I think that extra little bit of documentation would go a long way to making Drupal much more powerful.

fwiw the info seems to be coming in at arg(3) - if this helps anyone.

It is not easy to extract the context for all situations where this code would be involved. Please, see comments #44 and #45 in the following CCK issue: http://drupal.org/node/196518#comment-1658094

That issue is about token support for advanced views arguments, but the basic problem is the same as here. That means we have to live with the least worst of all solutions.

I'm going to mark this issue as fixed. We're constrained by CCK itself here. It is not possible to guess the context where allowed values list code is executed, so anything you do based on arg() or similar is prone to generate unexpected results, and we can do little about it here.

Sorry to re-open this old issue but could you explain why the array needs to be keyed? The key seems not to be used?

The key does not matter when using the "Autocomplete for existing field data" widget, however it does when using the "Autocomplete for allowed values list" widget, because it is the value of the array what's really stored in the field.

How about a working example, even if it doesn't work in all cases????

Here's a start, though not perfect:

$matches = array();
	$count=0;
		$result = db_query_range("SELECT title FROM {node} n WHERE n.type ='activiteit'", $string, 0, 10);
		while ($data = db_fetch_object($result)) {
			$matches[$count] = check_plain($data->title);
			$key=$matches[$count];
			$results[$matches[$count]] = $key;
			$count++;
		}

return $results;