php - SQL Server query only works if no variables are empty

Question

I'm trying to create a query that will filter a table based on different HTML dropdown selections. I have 5 separate dropdowns and whenever I make a selection, the value is passed into a query (and stored until cleared) which populates the data on the page. However, this query only displays data if all selections have been made. I'm not seeing any errors either. Can someone explain to me why this is happening and offer a solution to where it displays data no matter the amount of dropdown selections that have been made?

$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)

标签： phpsqlsql-server

Answer 1

Check for the length of the variable not the field

Instead of

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

Should be:

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