代码之家 › 专栏 › 技术社区 › Rataiczak24

SQL Server查询仅在没有变量为空时工作

sql-server sql php

Rataiczak24 · 技术社区 · 6 年前

我正在尝试创建一个查询,它将根据不同的HTML下拉选择过滤表。我有5个单独的下拉列表,每当我做出选择时,值就会被传递到一个查询(并存储到清除为止),这个查询填充页面上的数据。但是,此查询仅在进行了所有选择后显示数据。我也没有看到任何错误。有人能告诉我为什么会发生这种情况,并提供一个解决方案,它显示的数据,无论数量的下拉选择已经作出?

$q = ($_GET['q']);
$date = ($_GET['data']);
$val = ($_GET['val']);
$rep = ($_GET['rep']);
$group = ($_GET['group']);

$sql ="SELECT [Customer]
      ,[Collector Name]
      ,[Bill Date]
      ,[Days Until Next Action]
      ,[Next Action]
      ,[Next Bill Date]
      ,[Billed Status]
      ,[Billed Amount]
      ,[Total Current Balance]
      ,CAST([Payment Due Date] as DATE) as [Payment Due Date]
      ,[Notes]
      ,[Payment Status]
      ,[Line Status]
        FROM [Test_Spec_Bill]
        WHERE ([Collector Name] = '$q' OR datalength([Collector Name]) = 0)
        AND ([Date Group - Filter] = '$date' OR datalength([Date Group - Filter]) = 0)
        AND ([Billed Status] = '$val' OR datalength([Billed Status]) = 0)
        AND ([Parent Report Code] = '$rep' OR datalength([Parent Report Code]) = 0)
        AND ([Balance Group - Filter] = '$group' OR datalength([Balance Group - Filter]) = 0)

3 回复 | 直到 6 年前

Juan Carlos Oropeza 6 年前

检查变量的长度而不是字段的长度

WHERE ([Collector Name] = '$q' OR datalength([Collector Name]) = 0)

应该是:

WHERE ([Collector Name] = '$q' OR datalength('$q') = 0)
                                             ^^^^

Markov 6 年前

我不熟悉php,但是对于sql,我认为您首先需要检查变量是否不为null,然后使用它,也许在我们的php代码中,如果没有选择值,您可以将值默认为null。

SELECT
    [Customer]
   ,[Collector Name]
   ,[Bill Date]
   ,[Days Until Next Action]
   ,[Next Action]
   ,[Next Bill Date]
   ,[Billed Status]
   ,[Billed Amount]
   ,[Total Current Balance]
   ,CAST([Payment Due Date] AS DATE) AS [Payment Due Date]
   ,[Notes]
   ,[Payment Status]
   ,[Line Status]
FROM [Test_Spec_Bill]
WHERE (
('$q' IS NOT NULL
AND [Collector Name] = '$q')
OR DATALENGTH([Collector Name]) = 0
)
AND (('$date' IS NOT NULL
AND [Date Group - Filter] = '$date')
OR DATALENGTH([Date Group - Filter]) = 0
)
AND (('$val' IS NOT NULL
AND [Billed Status] = '$val')
OR DATALENGTH([Billed Status]) = 0
)
AND (('$rep' IS NOT NULL
AND [Parent Report Code] = '$rep')
OR DATALENGTH([Parent Report Code]) = 0
)
AND (('$group' IS NOT NULL
AND [Balance Group - Filter] = '$group')
OR DATALENGTH([Balance Group - Filter]) = 0
)

Joe McCarty 6 年前

DATALENGTH = 0 NULL 价值 DATALENGTH 退货 无效的 无效的 无效的 案例到真实使用 IFNULL

 WHERE ([Collector Name] = '$q' OR datalength(IFNULL([Collector Name], 0)) = 4)
        AND ([Date Group - Filter] = '$date' OR datalength(IFNULL([Date Group - Filter], 0)) = 4)
        AND ([Billed Status] = '$val' OR datalength(IFNULL([Billed Status], 0)) = 4)
        AND ([Parent Report Code] = '$rep' OR datalength(IFNULL([Parent Report Code],0)) = 4)
        AND ([Balance Group - Filter] = '$group' OR datalength(IFNULL([Balance Group - Filter],0)) = 4)