如何在Coldfusion中修改MySQL查询的结果(用HTML按钮/文本替换数据库整数)

I'm using datatables along with Jquery Mobile (server Coldfusion8, MySQL 5.0.88) to display tabular data. While this works fine, I'm trying to integrate AJAX into the table, so whenever the user pages or filters, the database is queried again and updated.

我使用了Jquery Mobile和datatable(服务器Coldfusion8, MySQL 5.0.88)来显示表格数据。虽然这样做很好，但我正在尝试将AJAX集成到表中，因此每当用户页面或过滤器出现时，都会再次查询并更新数据库。

Right now I'm trying to get it to work using this snippet

现在，我正在尝试使用这个片段使它工作

var userTable = $('#users).dataTable({
    "sAjaxSource": "path/to/handler.cfc?method=GetUsers",
    "aoColumns": [
        { "mDataProp": "id" , "sTitle": "ID"},
        { "mDataProp": "name" , "sTitle": "Name"},
        { "mDataProp": "surname" , "sTitle": "Surname"},
        { "mDataProp": "email" , "sTitle": "Email Address"}
      ]
    });

and inside CF:

和CF内:

<cffunction name="GetUsers" access="remote" output="false" returntype="any" returnformat="json"> 
    <cfquery name="local.qryUsers" datasource="dsn">
      SELECT id, name, surname, email FROM tblUsers
    </cfquery>

    <cfscript>
      local.strData = StructNew();
      local.strData['aaData'] = QueryToArray(local.qryCandidates);
      return local.strData;
     </cfscript>

</cffunction>

My problem is I need to modfiy some data from the query, for example I'm storing a column named status with values 1,2,3,4,5 and when I'm building the query results, I'm replacing the status number with the correct text in the correct language. Or I have a Jquery Mobile edit/delete controlgroup in every row, which I don't have in the database.

我的问题是我需要对查询中的一些数据进行modfiy，例如，我将一个名为status的列存储为值1,2,3,4,5，当我构建查询结果时，我正在用正确的语言用正确的文本替换状态号。或者我在每一行都有一个Jquery移动编辑/删除控制组，这是我在数据库中没有的。

Question:
Is there a way to add this to the query result returned from MySQL to Coldfusion = how can I (a) modifiy query results by looping over a column and replacing a number with some text (status) and (b) how can I add a full column to a query result (edit/delete) button, so I can then feed the whole thing into the AJAX response?

添加这个问题:有方法从MySQL Coldfusion =返回的查询结果如何(a)modifiy查询结果通过在一个循环体列和取代许多一些文本(状态)和(b)我怎么能全列添加到查询结果(编辑/删除)按钮,我就可以养活整个AJAX响应?

Thanks for help!

谢谢你的帮助!

2 个解决方案

#1

You can loop over the query and use QuerySetCell to modify contents of a column.

可以对查询进行循环，并使用QuerySetCell修改列的内容。

<cfloop query="local.qryUsers">
  <cfset QuerySetCell(local.qryUsers, "status", "your new value", local.qryUsers.currentRow)>
</cfloop>

You can use QueryAddColumn to add a column to a query.

您可以使用QueryAddColumn为查询添加列。

<cfset QueryAddColumn(local.qryUsers, "yourNewColumnName", [1,2,3])>

#2

Example for looping and changing

循环和更改的示例

<cfloop query="MyQuery">
    <cfscript>
        variables.myNewValue = "aaa #MyColumn#";
        QuerySetCell(MyQuery, "MyColumn", variables.myNewValue, CurrentRow);
    </cfscript>
</cfloop>

As for adding a column - try the QueryAddColumn() function

至于添加列——请尝试QueryAddColumn()函数

#1