Auto-suggest - pull out variables within query

Discussion in 'Programming' started by antonio12, Feb 16, 2010.

  1. #1
    Hello ColdFusion guru's,

    I have been trying to figure this out for days.

    So I have an auto-suggest that pulls in the following result sets,
    law firm name | last name, firstname,| ID

    What I can not seem figure out is out to get the specific variables out of the auto-suggest txt box and save them into the database.

    So here's the auto-suggest code:
    <!--- Autocomplete BEGIN --->
    <label for="getOC">Autosuggest Opposing Counsel<br /><input type="text" name="candidateID4" id="getOC" value="" /></label><br style="clear:both;" />
    <!--- Autocomplete END --->

    Here is the "getOC" function:
    function lookupAjax(){
    var oSuggest = $("#getOC")[0].autocompleter;

    oSuggest.findValue();

    return false;
    }

    $("#getOC").autocomplete("data/getOC.cfm", { autofill:true, extraParams:{iplitid:<cfoutput>#url.iplitid#</cfoutput>}, selectFirst:true,onFindValue:findValue,formatItem:formatItem,cellSeparator:"^" });
    </script>

    Here's the data from the getOC.cfm page:
    <cfsetting enablecfoutputonly="true">
    <cfparam name="q" default="" />
    <cfquery name="GetOC" datasource="datasource">
    SELECT * FROM tbl_sample
    WHERE CounselID NOT IN (SELECT CounselID FROM tbl_counsel
    WHERE IPlitID = #url.IPlitID#)
    AND LName LIKE <cfqueryparam value="#lCase(URL.q)#%" cfsqltype="cf_sql_varchar" /> OR FName LIKE <cfqueryparam value="#lCase(URL.q)#%" cfsqltype="cf_sql_varchar" /> OR Counsel LIKE <cfqueryparam value="#lCase(URL.q)#%" cfsqltype="cf_sql_varchar" />
    ORDER BY Counsel
    </cfquery>

    <cfoutput query="GetOC">
    #GetOC.Counsel# | #GetOC.lname#, #GetOC.fname#^#GetOC.CounselID##chr(10)#
    </cfoutput>

    Here is query used for the auto-suggest:
    <cfquery name="GetOC" datasource="#datasourcename#">
    SELECT * FROM tbl_sample
    WHERE CounselID NOT IN (SELECT CounselID FROM tbl_counsel
    WHERE IPlitID = #url.IPlitID#)
    ORDER BY Counsel
    </cfquery>

    --------------------------------------------------------------
    With all that being said, how do I need to pull just the CounselID, and the IPLitID OUT of the auto-suggest query and into a table.

    The query that I currently use does not allow me to do that:
    <cfif IsDefined("form.candidateID4")>
    <cfquery name="AddOC" datasource="#datasourcename#">
    INSERT INTO tbl_counsel (CounselID,IPLitID)
    SELECT #form.candidateID4#,#form.IPLitID#
    </cfquery>
    </cfif>

    PLEASE HELPPP!!!!

    THANK YOU!!!
     
    antonio12, Feb 16, 2010 IP