mysql - 选择和更新记录时查询为空错误？

Question

我想知道为什么我在检查在 Sqlyog 中运行良好的查询时收到此错误 "Query is Empty"。任何人都可以通过解释此查询中的错误来帮助我吗？任何帮助，将不胜感激。

这是我的代码。我在 Ubuntu 上使用 Railo 4.2.0。

<cfquery name="q" datasource="#getDSN()#">
    SELECT is_nav_item, nav_order, MAX(nav_order) AS maxNavOrder 
    FROM content
    WHERE id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#">
    OR site__id = <cfqueryparam value="#arguments.site__id#" />
</cfquery>

<cfquery datasource="#getDSN()#">
    <!--- 
       check to see if existing record has 0 and in arguments it is 
       passed as 1 then we are going to update the nav order by selecting 
       max nav order in the table then incrementing the maxNavOrder by 1
    --->
    <cfif q.is_nav_item EQ 0 AND arguments.is_nav_item EQ 1>
        UPDATE content
        SET nav_order = #q.maxNavOrder+1#
        WHERE id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
        AND site__id = <cfqueryparam value="#arguments.site__id#" />
    </cfif>

    <!--- 
        check to see if the existing record have is_nav_item eq 1 and in 
        the arguments it is 0 if it is then its going to update the nav_order 
        to 0... so that we can prevent it being updating the nav order in case 
        someone not updating nav_item but something else
    --->
    <cfif q.is_nav_item EQ 1 AND arguments.is_nav_item EQ 0>
        UPDATE content
        SET nav_order = 0
        WHERE id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
        AND site__id = <cfqueryparam value="#arguments.site__id#" />
        ;
    </cfif>
 </cfquery>

Answer 1

您的查询中有 if 语句...如果它们的计算结果都不为真，则您的查询可能是一个空查询。

为什么不执行 IF 并将整个查询放在 IF 语句中...这样就无法获得其中没有任何文本的 CFQUERY。

<!--- 
  check to see if existing record has 0 and in arguments it is passed as 1 
  then we are going to update the nav order by selecting max nav order in 
  the table then incrementing the maxNavOrder by 1
--->
<cfif q.is_nav_item EQ 0 AND arguments.is_nav_item EQ 1>
     <cfquery datasource="#getDSN()#">
        UPDATE content
        SET    nav_order = #q.maxNavOrder+1#
        WHERE  id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
        AND site__id = <cfqueryparam value="#arguments.site__id#" />
     </cfquery>
</cfif>

<!--- 
    check to see if the existing record have is_nav_item eq 1 and in the 
    arguments  it is 0.  if it is then its going to update the nav_order 
    to 0... so that we can prevent it being updating the nav order in case 
    someone not updating nav_item but something else
--->
<cfif q.is_nav_item EQ 1 AND arguments.is_nav_item EQ 0>
    <cfquery datasource="#getDSN()#">
       UPDATE content
       SET    nav_order = 0
       WHERE  id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
       AND    site__id = <cfqueryparam value="#arguments.site__id#" />
    </cfquery>
</cfif>