Solved

jQuery Autocomplete & Autofill

Posted on 2013-01-17
13
605 Views
Last Modified: 2013-01-17
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
0
Comment
Question by:t3chguy
  • 7
  • 6
13 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 38790210
are you ok to use jQuery UI autocomplete? The one you're using is deprecated
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38790252
I'm open to anything, yes.
0
 
LVL 82

Assisted Solution

by:leakim971
leakim971 earned 500 total points
ID: 38790265
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
 
LVL 1

Author Comment

by:t3chguy
ID: 38790285
[{"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
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 38790317
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
 
LVL 1

Author Comment

by:t3chguy
ID: 38790337
Perfect!

The only other question then would be how to pass those fields off to mysql when the form is submitted?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

by:leakim971
ID: 38790339
as usual... with hidden or visible field, just be sure to give them an name attribute
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38790360
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
 
LVL 82

Expert Comment

by:leakim971
ID: 38790370
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
 
LVL 1

Author Comment

by:t3chguy
ID: 38790385
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
 
LVL 1

Author Comment

by:t3chguy
ID: 38790390
Nevermind, got it!
0
 
LVL 82

Expert Comment

by:leakim971
ID: 38790392
$("#compname").val(ui.item.label + "(" + ui.item.id + ")");
0
 
LVL 1

Author Closing Comment

by:t3chguy
ID: 38790394
Excellent solution and patience! Thank you greatly!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Hi all! Recently there was EE question and the person wanted to have a multi-column textbox <div> selection, so as a first step to answer I provided a link but that was not complete with JavaScript selection, but had a good style sheet. So as a ques…
Introduction Got endorsements from your clients?  Great!  There is almost nothing better than word-of-mouth advertising.  But how can you do that on the internet?  Sure you can make a page for endorsement quotations and list them all, but who is …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now