custom js to add a database lookup

This site is best viewed in a modern browser with JavaScript enabled.

custom js to add a database lookup

christopherbrooks

Can anyone give a simple example of a custom js that will
--take a value from a field on the form ( element_8 for example),
--use that to call a php which would do a database lookup, and return a value
-- then display the returned value on the same form

I know the custom js URL needs to be referenced in the form properties Load Custom Javascript File
I had this working in an older version of Machform, but it does not seem to be working for me now. Does not give an error of any type, even if the custom js URL does not exist. SO I must be missing some requirement in how to create and reference things.

topangan

I don't have a fix but I am interested in having this functionality so I will be following this post...

Chambler

You could do something like (very simplified no error checks etc)

$(function() {
  const path = window.location.pathname;
  const formId = $(input[name="form_id"]).val();

  if (path.includes('view.php')) { // only execute when on view.php not edit etc.
    const element8 = $("#element_8");
    element8.on('change', () => {
      // do something when when element8 changes
      // assuming your database lookup script returns JSON
      $.getJSON('https://yourscript.url/database-lookup.php?form=' + formId)
        .done((result) => {
          alert(result.message); // show the message variable in the json as an alert
        });
    });
  }
});

We've used this to build entire booking workflows on this with availability being checked and multiple dropdowns being updated based on availability.

Depending on what you are doing you may also want to add an event listener to the form submit to just validate the results of your database lookup are still valid especially if the data you are returning is likely to have changed between the request and them submitting the form.

christopherbrooks

Thanks to some helpful guidance, I was able to get this working. I post my working code here for the benefit of anyone who might want to do something similar.

The requirement: User enters a key value; js and php do a lookup to see if the value is in my database, returning some information if yes, or a not found message. Page logic uses the result to determine if the user is directed to the page to give details (if not found), or bypass details if found.

I have a folder SEI under the standard hooks folder. In it are the files:
custom_code_SEI.js, custom_code_SEI.php, and ajax-loader.gif

The form has custom js enabled, with the URL https://showentriesinfo.com/smartform/hooks/SEI/custom_code_SEI.js

The php does the lookup of the key value from the user input, and returns results:
<?php
ini_set('display_errors',1);
require('../../config.php');
require('../../includes/helper-functions.php');
require('../../includes/db-core.php');

$dbh = mf_connect_db();
$query = "SELECT Dogname, DogID FROM vQESEIDogList WHERE AKCRegNo = ?";
$params = array($_POST['input_search_from']);
$sth = mf_do_query($query,$params,$dbh);
$row = mf_do_fetch_result($sth);
if($row != FALSE){
die(json_encode($row));
}else{
$nodata = array("Dogname" => "Not on file at ShowEntries",
"DogID" => "0");
die(json_encode($nodata));
}
?>

The javascript file:
$(function(){
var form_db = "SEI";
var input_search_from = "#element_8";// AKC Number input
var input_to_populate_1 = '#element_10';// Dog
var input_to_populate_2 = '#element_53';// DogID

var x_timer;
$(input_search_from).keyup(function (e){
	clearTimeout(x_timer);
	x_timer = setTimeout(function(){	
		makeLookUp($(input_search_from).val());
	}, 1000);
});
function makeLookUp(input_search_from){
	$(input_search_from).next('img').remove();	
	$(input_search_from).after('<img src="hooks/'+form_db+'/ajax-loader.gif" style="margin-left:1%"/>');
	
	$(input_to_populate_1).val(" "); 
	$(input_to_populate_2).val("0"); 

	$.post('hooks/'+form_db+'/custom_code_'+form_db+'.php',{
		'input_search_from':input_search_from
	}, 
	function(data) {
		$(input_search_from).next('img').remove();
		$(input_to_populate_1).val(data.Dogname); 
		$(input_to_populate_2).val(data.DogID);
	},
	"json"
	);
}

});