jQuery Autocomplete & Autofill

I have a form that I'm trying to get to both autocomplete and autofill multiple fields/

When the users type a company name in the search, I want them to be able to click a name in the drop down that is made, and then the program fetches the rest of the data about the company from a mysql table.

The fields, aside from the name, are address, city, state, zip.

I have the autocomplete field working , but stuck on how to go about the next step where clicking the name will pull the rest of the information about the company from the database, without actually submitting the form, if possible.

<link rel="stylesheet" type="text/css" href="../../../js/autocomplete/jquery.autocomplete.css" />
<script type="text/javascript" src="../../../js/autocomplete/jquery.autocomplete.js"></script>

<script type="text/javascript">
$(function () { 
    $("#usedefaultbill").click(function () {
        $("#newdivbill").hide();
     });
    $("#usealternatebill").click(function () {
        $("#newdivbill").show();
    });
	
    $("#usedefaultship").click(function () {
        $("#newdivship").hide();
     });
    $("#usealternateship").click(function () {
        $("#newdivship").show();
    });

 $("#compname").autocomplete("autocomplete.php", {
	width: 260,
	matchContains: true, 
	mustMatch: true,
	// minChars: 3,
	//multiple: true,
	//highlight: true,
	//multipleSeparator: ",",
	selectFirst: false      
});

$("#compname").result(function(event, data, formatted) {
	$("#kdaccount").val(data[1]);
	$("#compid").val(data[2]);
	$("#submit").trigger('click');
});

$("#compid").change( function () {
  $("#submit").trigger('click');
}); 
 
});
</script>
<form id="createNew" method="POST" enctype="multipart/form-data" action="<?=$_SERVER['PHP_SELF']?>?kdaccount=<?=$vendorid?>&action=poinfo">

							<div id="insideleft">Search for Company:</div>
							<div id="insideright">
								<input type="text" name="compname" id="compname" size="35" class="formFields required" /><?=$cid?></td>
								<input type="hidden" name="compid" id="compid" value = "<?=$cid?>">
								<input type="hidden" name="kdaccount" id="kdaccount" value = "<?=$kdaccount?>">
							</div>
							<div style="clear:both;"></div>
							
							<div id="insideleft">Company Name:</div>
							<div id="insideright"><input type="text" name="shipCompName" class="formFields"></div>

							<div style="clear:both;"></div>
							
							<div id="insideleft">Address:</div>
							<div id="insideright"><input type="text" name="shipAddress" class="formFields"></div>

							<div style="clear:both;"></div>
							
							<div id="insideleft">City:</div>
							<div id="insideright"><input type="text" name="shipCity" class="formFields"></div>

							<div style="clear:both;"></div>
							
							<div id="insideleft">State:</div>
							<div id="insideright"><input type="text" name="shipState" class="formFields"></div>

							<div style="clear:both;"></div>
							
							<div id="insideleft">Zip:</div>
							<div id="insideright"><input type="text" name="shipZip" class="formFields"></div>
							
							<div style="clear:both;"></div>
							
							<div id="insideleft">Attention:</div>
							<div id="insideright"><input type="text" name="shipAttn" class="formFields"></div>

<div align="center"><input type="submit" name="createPO" value="Create PO & Continue"></div>		

</form>

Open in new window


autocomplete.php
<?php

include("../../includes/sqlconnect.php");

$q = strtolower($_GET["q"]);
if (!$q) return;

$sql = "SELECT name, kdaccount, nameid, add1, town, postcode, state FROM dw_names WHERE (kdaccount LIKE '%$q%' OR name LIKE '%$q%') AND (kstatus = 'A' OR kstatus = 'S') AND name NOT LIKE '%Do Not%' AND hidden = '0' ORDER BY kdaccount, name LIMIT 50";
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) 
	{
	$cid = $rs['nameid'];
	$cname = $rs['name'];
	$kdaccount = $rs['kdaccount'];
	$address  = $rs['add1'];
	$town = $rs['town'];
	$postcode = $rs['postcode'];
	$state = $rs['state'];
	
	echo "$cname ($kdaccount)|$kdaccount|$cid | $address|$town|$postcode|$state\n";
	}
?>

Open in new window

Screenshot.png
LVL 1
t3chguyAsked:
Who is Participating?
 
leakim971Connect With a Mentor PluritechnicianCommented:
thanks, replace you whole javascript and jquery.autocomplete css by the following :
(you need to type two chars in the textbox to get answer)
<link rel="stylesheet" type="text/css" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/themes/base/jquery-ui.css" />
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>
<script type="text/javascript">
$(function () { 
    $("#usedefaultbill").click(function () {
        $("#newdivbill").hide();
     });
    $("#usealternatebill").click(function () {
        $("#newdivbill").show();
    });
	
    $("#usedefaultship").click(function () {
        $("#newdivship").hide();
     });
    $("#usealternateship").click(function () {
        $("#newdivship").show();
    });

	$("#compname").autocomplete({
		source: "autocomplete.php",
		minLength: 2,
		select: function(event, ui) {
			event.preventDefault();
			$("#compid").val(ui.item.id)
			$("#compname").val(ui.item.label);
			$(':text[name=shipCompName]').val(ui.item.label);
			$(':text[name=shipAddress]').val(ui.item.address);
// add the other fields you want to fill here
		}
	});

/*
$("#compname").result(function(event, data, formatted) {
	$("#kdaccount").val(data[1]);
	$("#compid").val(data[2]);
	$("#submit").trigger('click');
});
*/
$("#compid").change( function () {
  $("#submit").trigger('click');
}); 
 
});
</script>

Open in new window

0
 
leakim971PluritechnicianCommented:
are you ok to use jQuery UI autocomplete? The one you're using is deprecated
0
 
t3chguyAuthor Commented:
I'm open to anything, yes.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
leakim971Connect With a Mentor PluritechnicianCommented:
ok, the first step is to modify your php code with the following one.
please test it before we continue. to test put its url in your web browser address bar:
http://localhost/path/to/autocomplete.php?term=a
and post what you get here
<?php
    $json =  array();
    include("../../includes/sqlconnect.php");
    
    $q = strtolower($_GET["term"]);
    if(!$q) return;
    
    $sql = "SELECT name, kdaccount, nameid, add1, town, postcode, state FROM dw_names WHERE (kdaccount LIKE '%$q%' OR name LIKE '%$q%') AND (kstatus = 'A' OR kstatus = 'S') AND name NOT LIKE '%Do Not%' AND hidden = '0' ORDER BY kdaccount, name LIMIT 50";
    $rsd = mysql_query($sql);
    while($rs = mysql_fetch_array($rsd)) 
    {
        $row_array["id"] = $rs['nameid'];
        $row_array["label"] = $rs['name'];
        $row_array["value"] = $rs['kdaccount'];
        $row_array["address"]  = $rs['add1'];
        $row_array["town"] = $rs['town'];
        $row_array["postcode"] = $rs['postcode'];
        $row_array["state"] = $rs['state'];

        array_push($json, $row_array);        
    }

    header('Content-Type: application/json');
    echo json_encode($json);
?>

Open in new window

0
 
t3chguyAuthor Commented:
[{"id":"100000","label":"Heritage Industrial Services","value":"100000","address":"15 Hendrickson Dr","town":"Cream Ridge","postcode":"08514","state":"NJ"},{"id":"100002","label":"Packard Mechanical","value":"100002","address":"19 Main St","town":"Cream Ridge","postcode":".","state":"NJ"},{"id":"100006","label":"NCG Electrical Contractors","value":"100006","address":"5 Marlen Drive","town":"Robinsville","postcode":"08691","state":"NJ"},{"id":"100007","label":"Alliance Roofing and Sheet Metal","value":"100007","address":"4215 Eastern Ave","town":"Baltimore","postcode":"21224","state":"MD"},{"id":"100010","label":"Great Lakes Roofing","value":"100010","address":"W194N11095 Kleinmann Dr.","town":"Germantown","postcode":"53022","state":"WI"},{"id":"100012","label":"ADS Acme Dock Specialists","value":"100012","address":"3030 Gillham Rd","town":"Kansas City","postcode":"64108","state":"MO"},{"id":"100015","label":"Adams Equipment Rental","value":"100015","address":"154 Turnbull Ave","town":"Hamilton","postcode":"08610","state":"NJ"},{"id":"100016","label":"Bechtel Power Corporation","value":"100016","address":"815 West Homestead Boulevard #417","town":"Homestead","postcode":"33030","state":"FL"},{"id":"100017","label":"Dannegger Brothers Contracting","value":"100017","address":"2385 Adie Rd.","town":"Maryland Heights","postcode":"63043","state":"MO"},{"id":"100020","label":"Southland Steel Fabricators","value":"100020","address":"251 Greensburg St","town":"Greensburg","postcode":"70441","state":"LA"},{"id":"100021","label":"Morgan's Mechanical Services","value":"100021","address":"781 Highway 10","town":"Greensburg","postcode":"70441-6044","state":"LA"},{"id":"100036","label":"Arkel Constructors Inc","value":"100036","address":"1048 Florida Blvd","town":"Baton Rouge","postcode":"70802-4636","state":"LA"},{"id":"100040","label":"Formosa Plastics Corp, LA","value":"100040","address":"70805 Gulf States Rd","town":"Baton Rouge","postcode":"70805-0271","state":"LA"},{"id":"100041","label":"Percy J Matherne Contractor","value":"100041","address":"5762 Hooper Rd","town":"Baton Rouge","postcode":"70811-2419","state":"LA"},{"id":"100048","label":"Placid Refining Co LLC","value":"100048","address":"1940 La Highway 1 N","town":"Port Allen","postcode":"70767-3347","state":"LA"},{"id":"100050","label":"Beard Construction Group LLC","value":"100050","address":"3970 Rosedale Rd","town":"Port Allen","postcode":"70767-4307","state":"LA"},{"id":"100053","label":"Catalyst Old River","value":"100053","address":"Highway 15","town":"Lettsworth","postcode":"70753","state":"LA"},{"id":"100055","label":"Elmwood Marine Services Inc","value":"100055","address":"6607 La 44","town":"Convent","postcode":"70723-2231","state":"LA"},{"id":"100056","label":"Occidental Chemical Corp","value":"100056","address":"7377 Highway 3214","town":"Convent","postcode":"70723-2503","state":"LA"},{"id":"100057","label":"Motiva Enterprises LLC","value":"100057","address":"10700 La 44","town":"Convent","postcode":"70723-3016","state":"LA"},{"id":"100058","label":"Ferrara Fire Apparatus Inc","value":"100058","address":"27855 James Chapel Rd N","town":"Holden","postcode":"70744-5309","state":"LA"},{"id":"100061","label":"Wilco Industrial Services LLC","value":"100061","address":"2002 False River Dr Ste A","town":"New Roads","postcode":"70760-2610","state":"LA"},{"id":"100064","label":"Entergy Operations Inc","value":"100064","address":"River Bend Stn","town":"Saint Francisville","postcode":"70775","state":"LA"},{"id":"100079","label":"Pointer Smith Contracting Corp","value":"100079","address":"31093 Terminal Ct.","town":"Geismar","postcode":"70734","state":"LA"},{"id":"100086","label":"Shaw Group Inc","value":"100086","address":"36445 Old Perkins Rd","town":"Prairieville","postcode":"70769-3207","state":"LA"},{"id":"100087","label":"MB Heating & Air Conditioning","value":"100087","address":"10155 Highway 431","town":"Gonzales","postcode":"70737-7955","state":"LA"},{"id":"100089","label":"Bakercorp","value":"100089","address":"35167 Highway 30","town":"Geismar","postcode":"70734-3408","state":"LA"},{"id":"100093","label":"Lemoine Industrial Group LLC","value":"100093","address":"24870 Highway 405","town":"Plaquemine","postcode":"70764-4148","state":"LA"},{"id":"100095","label":"Louisiana Energy & Power Auth","value":"100095","address":"59335 W. W. Harleaux Blvd.","town":"Plaquemine","postcode":"70764","state":"LA"},{"id":"100098","label":"Post Tension Slabs Inc","value":"100098","address":"11750 Cloverland Court","town":"Baton Rouge","postcode":"70809-8208","state":"LA"},{"id":"100099","label":"Graham Construction LLC","value":"100099","address":"4740 Jones Creek Rd","town":"Baton Rouge","postcode":"70817-1525","state":"LA"},{"id":"100102","label":"Record & Associates Constr","value":"100102","address":"6425 Quinn  Drive","town":"Baton Rouge","postcode":"70817-4544","state":"LA"},{"id":"100103","label":"Bernhard Brothers Mechanical","value":"100103","address":"13641 Airline Hwy","town":"Baton Rouge","postcode":"70817-5922","state":"LA"},{"id":"100111","label":"River City Air Conditioning","value":"100111","address":"11232 Cedar Park Ave","town":"Baton Rouge","postcode":"70809-4124","state":"LA"},{"id":"100118","label":"Arrighi Construction LLC","value":"100118","address":"15055 Jefferson Hwy","town":"Baton Rouge","postcode":"70817-6230","state":"LA"},{"id":"100128","label":"United Scaffolding Inc","value":"100128","address":"15981 Airline Highway","town":"Baton Rouge","postcode":"70817","state":"LA"},{"id":"100130","label":"Living Quarters Technology Inc","value":"100130","address":"7939 Highway 167 South","town":"Abbeville","postcode":"70510","state":"LA"},{"id":"100134","label":"Taylor Mechanical Services Inc","value":"100134","address":"6654 Van Gogh Ave","town":"Baton Rouge","postcode":"70806-2760","state":"LA"},{"id":"100138","label":"Salco Construction Inc","value":"100138","address":"8484 Athens Ave","town":"Baton Rouge","postcode":"70814-2303","state":"LA"},{"id":"100141","label":"Cangelosi Ward, General Contr","value":"100141","address":"9512 Brookline Ave","town":"Baton Rouge","postcode":"70809-1430","state":"LA"},{"id":"100148","label":"Neel-Schaffer Inc","value":"100148","address":"772 Howard Ave","town":"Biloxi","postcode":"39530-3820","state":"MS"},{"id":"100150","label":"J O Collins Contractor Inc","value":"100150","address":"206 Iberville Dr","town":"Biloxi","postcode":"39531-4347","state":"MS"},{"id":"100153","label":"Starks Contracting Co Inc","value":"100153","address":"1538 Popps Ferry Rd","town":"Biloxi","postcode":"39532-2203","state":"MS"},{"id":"100162","label":"Whitesell Green Yates & Sons","value":"100162","address":"605 Parade Ln","town":"Biloxi","postcode":"39534-2044","state":"MS"},{"id":"100165","label":"MCC Mechanical LLC","value":"100165","address":"24101 Spyders Dr","town":"Pass Christian","postcode":"39571-9414","state":"MS"},{"id":"100168","label":"VT Halter Marine Inc","value":"100168","address":"7801 Trinity Dr","town":"Escatawpa","postcode":"39552","state":"MS"},{"id":"100171","label":"VT Halter Marine Inc","value":"100171","address":"5801 Elder Ferry Rd","town":"Moss Point","postcode":"39563-0012","state":"MS"},{"id":"100173","label":"Utility Optimization Group LLC","value":"100173","address":"6917 Stennis Blvd","town":"Pascagoula","postcode":"39562-7601","state":"MS"},{"id":"100174","label":"Herman Cronier & Sons Inc","value":"100174","address":"8201 Highway 63","town":"Moss Point","postcode":"39562-6010","state":"MS"},{"id":"100177","label":"Foster Air Conditioning","value":"100177","address":"3416 Suter Rd","town":"Gautier","postcode":"39553-5445","state":"MS"}]
0
 
t3chguyAuthor Commented:
Perfect!

The only other question then would be how to pass those fields off to mysql when the form is submitted?
0
 
leakim971PluritechnicianCommented:
as usual... with hidden or visible field, just be sure to give them an name attribute
0
 
t3chguyAuthor Commented:
So the value would be set equal to the jquery value?

<input type="text" name="shipCompName" class="formFields" value="$(':text[name=shipZip]').val(ui.item.postcode);">
0
 
leakim971PluritechnicianCommented:
no don't touch your inline html
 just put $(':text[name=shipZip]').val(ui.item.postcode);

where I put this line : // add the other fields you want to fill here
0
 
t3chguyAuthor Commented:
I'm an idiot, the value is already in the text box so a simple post that field will do it.

Sorry just one other thing.

When the autocomplete complies the list of companies, is there any way to get the account number in parenthesis next to the company name?

Sometimes we have multiple companies with the same name, so showing the account number as well will help distinguish between accounts.
0
 
t3chguyAuthor Commented:
Nevermind, got it!
0
 
leakim971PluritechnicianCommented:
$("#compname").val(ui.item.label + "(" + ui.item.id + ")");
0
 
t3chguyAuthor Commented:
Excellent solution and patience! Thank you greatly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.