从ColdFusion查询中形成JSON，以便在jQuery自动完成中使用 [英] forming json from coldfusion query for use in jquery autocomplete

问题描述

我很困惑.我有一个现有的自动完成功能，当从ColdFusion自动建议输入调用时可以使用.现在，我正在尝试将页面转换为使用jQuery自动完成输入，并且无法使其正常工作.这是autosuggest.cfc中的现有功能:

I'm stumped. I have an existing autocomplete function which worked when called from a ColdFusion autosuggest input. Now, I'm trying to convert the page to use a jQuery autocomplete input, and can't make it work. Here's the existing function, in autosuggest.cfc:

<cffunction name="lookupSerialNumber" access="remote" returntype="Array" >
    <cfargument name="search" type="any" required="false" default="">

    <!--- Define variables --->
    <cfset var data="">
    <cfset var result=ArrayNew(1)>

    <!--- Do search --->
    <cfquery name="data">
        SELECT DISTINCT SERIAL_NUMBER
        FROM myTable
        WHERE SERIAL_NUMBER LIKE '%#trim(ARGUMENTS.search)#%'
        ORDER BY SERIAL_NUMBER
    </cfquery>

    <!--- Build result array --->
    <cfloop query="data">
        <cfset ArrayAppend(result, list)>
    </cfloop>

    <!--- And return it --->
    <cfreturn result />
</cffunction>

当我在输入"01"后从自动完成输入中调用它时，控制台将显示如下响应:

When I call it from my autocomplete input after entering "01", the Console shows a response like this:

   ["0000003001","0100002000","0100002001","0100002002","0100002003","0100002004",7300000100,7300000101,7300000102,7300000103,7300000104,7300000105,7300000107,7300000108,7300000109,7300000110,7300000111,7300000112]

请注意，前六个响应在双引号内，而其他则不在.自动完成功能会显示前六个响应，但不会显示其余响应.

Note that the first six responses are inside double quotes, while the rest are not. The autocomplete shows the first six responses, but not the rest.

所以这是我的第一个问题.第二个是我希望将响应同时注入到文本和输入值中.当前，只有文本显示响应.我并不感到惊讶，因为我只返回一个值.但是，我不知道该怎么做.

So that's my first problem. The second is that I want the response to be injected into both the text and the value of the input; currently, only the text shows the response. I'm not surprised, as I'm only returning a single value. However, I can't figure out how to accomplish this.

我尝试使用解析函数初始化自动完成功能，如下所示:

I've tried initializing the autocomplete with a parse function, like so:

$(document).ready(function(){
        $('#myInputID').autocomplete(
            {source: function(request, response) {
                $.ajax({
                    url: "cfc/autoSuggest.cfc?method=lookupSerialNumber>&returnformat=json",
                    dataType: "json",
                    data: {
                      search: request.term,
                      maxRows: 10
                    },
                    success: function(data) {
                      response(data);
                    }                   
                })
            },
            parse: function(data){
                return $.map(data, function(item) {
                    return { data: item, value: item, result: item };
                });
            }
        });
    });

但是这什么也做不了.由于解析功能不在自动完成API中，因此我在这里视而不见.

but this accomplishes nothing. Since the parse function isn't in the autocomplete API, I'm flying blind here.

我认为这些问题是足够相关的，因此不能将它们分为两个问题.如果您认为我应该告诉我.一如既往，谢谢.

I figured that these problems were related enough to not split them out into two questions; let me know if you think I should. As always, thanks.

推荐答案

将查询结果放入结构中，然后将其添加到数组中.然后转换为json.自动完成功能要求返回json以及label或value字段，或同时包含这两个字段.这是一个示例:

Put your query results in a structure and add that to an array. Then convert to json. The autocomplete is expecting json back and either a label or a value field or both. Here is an example:

<cffunction name="lookupSerialNumber" access="remote" returntype="String" >
    <cfargument name="search" type="any" default="">

    <!--- Define variables --->
    <cfset var data="">
    <cfset var result=ArrayNew(1)>
    <cfset var returnStruct = "">


    <!--- Do search --->
    <cfquery name="data">
        SELECT DISTINCT SERIAL_NUMBER
        FROM myTable
        WHERE SERIAL_NUMBER LIKE <cfqueryparam value="%#trim(ARGUMENTS.search)#%" 
                                     cfsqltype="cf_sql_varchar">

        ORDER BY SERIAL_NUMBER
    </cfquery>

    <!--- Build result array --->
    <cfloop query="data">
        <cfset returnStruct = StructNew() />
        <cfset returnStruct["label"] = SERIAL_NUMBER />

        <cfset ArrayAppend(result,returnStruct) />
    </cfloop>

    <!--- And return it --->
    <cfreturn serializeJSON(result) />
 </cffunction>

如果serializeJSON功能在所使用的任何版本的CF中都可用，则可以使用.如果没有，请手动构建json字符串.

You can use the serializeJSON function if it's available in whatever version of CF you are using. If not, build the json string by hand.

这里有个例子: http://www.jensbits.com/2010/03/18/jquery-ui-autocomplete-with-coldfusion/

这篇关于从ColdFusion查询中形成JSON，以便在jQuery自动完成中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！