Autocomplete field with values from DB

Hello,

I'm trying to create a multipage form with first field as autocomplete field for values from database. I was looking at demo autocomplete form which uses text field for autocomplete. Is this possible with dropdown field or can I get values from DB also in text field?

Any guidance would be appreciated!

Regards

Hi klox7,

Sorry, I don't understand what you are trying to do here?

The autocompleter gets values from a DB table and shows them in a text input.

The dynamic drop-down settings will let you set drop-down options from a table in various ways.

Bob

Hi, thanks for answer. I just had to add new event with DB Read for autocomplete and now it's started to loading but without results. There is error in console for select2.min.js:22 - Uncaught TypeError: Cannot read property 'length' of null. Do you maybe know what could be the issue.

Hi klox7,

It looks as though Select2 is a jQuery plug-in - not something that ChronoForms uses as far as I know. Has it been added to your form?

Bob

This file is a part of your library in libraries/cegcore/assets/select2

Hi klox7,

You are right - I'd never seen that before.

Possibly your DB Read event isn't returning anything this that the AutoCompleter can recognise. That would explain the zero length.

Please post a link to the form so I can take a quick look.

Bob

Hi,

This is private content

In autocompleter field selector I have Field ID and in the AJAX form event I have name of the event which has Read DB in it.

Hi Klox7,

There is nothing being returned by the AJAX event. The URL that is being sent is

index.php?option=com_chronoforms5&chronoform=claimandregister&event=resort_list&tvout=ajax&title=tr&_=1437570022493

where tr is the entry I typed - the bit at the end &_=1437570022493 looks odd and may be causing a problem.

It looks to me as if that last item is a time-stamp of some kind but I can't see where it is being added.

What code do you have in the resort_list event?

Bob

I have code in conditions. This are the settings.

Enabled -> Yes
Table name -> selected table name
Multi read -> Yes
Enable Model ID -> No
Conditions -> <?php return array('type_id' => '1','published'=>'1'); ?>

Regarding AJAX form event it says Form event used to load the results, the results should be an array encoded using json_encode. Must I do something here?

Hi klox7,

If you look at the code in the demo form - scroll down past all the countries - you will see code like this:

$json = array();
foreach ( $countries as $country ) {
  if ( !empty($form->data['tag']) AND stripos($country, $form->data['tag']) === false ) {
    continue;
  }
  $json[] = array('id' => $country, 'text' => $country);
}
echo json_encode($json);
?>

You need something similar.

Also the Conditions you are setting do not include the data the user typed - the 'tr' from my example and so will always return the full list which isn't very helpful. You need a LIKE clause in there too.

Bob

Hi, this is what works for me now. It is to get record title from Cobalt CCK. If anybody needs it here it is:

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = 'SELECT title FROM #__js_res_record WHERE type_id=1 AND published=1';
$db->setQuery($query);
$record_title = $db->loadColumn();

foreach($record_title as $title){
	if(!empty($form->data['title']) AND stripos($title, $form->data['title']) === false){ // title is Field name in autocompletter
		continue;
	}
	$json[] = array('id' => $title, 'text' => $title);
}
echo json_encode($json);

I'm having some problems with autocompleter and SSL. I forced my chronoform menu item to be loaded over https and when I want to search through autocompete it gives me this error in console:

Mixed Content: The page at 'https://domain.com/owners' was loaded over HTTPS, but requested an insecure XMLHttpRequest endpoint 'http://domian.com/index.php?option=com_chronoforms5&chronoform=formname&event=resort_list&tvout=ajax&title=hj&_=1438090288944'. This request has been blocked; the content must be served over HTTPS.

Hi klox7,

Please check if there is a livesite setting in the site config.php file. If there is try removing it - that's the most frequent cause of https problems that I see.

Bob

Hi Bob,
live site setting in config file is empty.
Regards

Hi kloz7,

Then you may have a full URL in the AutoCompleter action, please change that to use https . . . or leave off the domain so it starts with index.php? . . .

Bob

Do you mean AJAX URL? If so it is empty because I use AJAX form event. Must I use Ajax URL?

Hi klox7,

Hmmm . . . this gets a bit technical but I think that there is a bug deep in the CF code that will fail to recognise the https setting on some servers.

In the file /libraries/cegcore/libs/url.php around line 31 is this function

public static function domain(){
		$dURL = (isset($_SERVER['HTTPS']) AND ($_SERVER['HTTPS'] == 'on')) ? 'https://' : 'http://';
		$dURL .= !empty($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : $_SERVER['SERVER_NAME'];
		if($_SERVER['SERVER_PORT'] != '80' AND $_SERVER['SERVER_PORT'] != '443'){
			if(strpos($dURL, ':'.$_SERVER['SERVER_PORT']) === false){
				$dURL .= ':'.$_SERVER['SERVER_PORT'];
			}
		}
		return $dURL;
	}

The == 'on' in the second line is detecting the https. But it won't work if your server isn't using 'on' as the indicator.

I found this StackOverFlow answer that suggests an alternative - the thread there has some other suggestions too,

Here's the modified version - I also changed the layout a it so that I could see what is happening

public static function domain() {
  if ( (!empty($_SERVER['HTTPS']) AND $_SERVER['HTTPS'] !== 'off') 
      OR $_SERVER['SERVER_PORT'] == 443 ) {
    $dURL = 'https://';
  } else {
    $dURL = 'http://';
  }

  if ( !empty($_SERVER['HTTP_HOST']) ) {
    $dURL .= $_SERVER['HTTP_HOST'];
  } else {
    $dURL .= $_SERVER['SERVER_NAME'];
  }

  if ( $_SERVER['SERVER_PORT'] != '80' AND $_SERVER['SERVER_PORT'] != '443') {
    if ( strpos($dURL, ':'.$_SERVER['SERVER_PORT']) === false ) {
      $dURL .= ':'.$_SERVER['SERVER_PORT'];
    }
  }
  return $dURL;
}

I don't have https on my sites so can’t test this; please do if you like but keep a backup.

Bob