CODEWITHSUNDEEP
mysqlsql
Chris Sanders
Chris Sanders

Reputation: 23

Allowing Optional Parameters for MySQL Query

I have a search page that has multiple fields that are used to create a refined search. Every field is optional. I'm trying to start crafting my sql query so that it will work given the proper variables but I'm having trouble.

Here is the SQL query I currently have:

SELECT
  indicator.indid,
  indicator.indicator,
  indtype.indtype,
  provider.provider,
  report.report,
  actor.actor
FROM
  actor,
  indicator,
  indtype,
  report,
  provider
WHERE
  indicator.indtypeid = indtype.indtypeid
  AND indicator.actorid = actor.actorid
  AND indicator.reportid = report.reportid
  AND report.providerid = provider.providerid
  AND indicator.indicator LIKE '%$indicator%'
  AND indicator.indtypeid = $indtypeid;

Whenever I provide an indicator and an indtypeid, the search works just fine. However, when I leave the indtypeid field blank, and have the variable set to * (as its default value), the query returns no results. I've tried playing with the query manually and it doesn't seem to like the * or a % sign. Basically, if only an indicator is specified and no indtypeid is specified, I want to return all indicators for all indtypeids.

I'm sure I'm missing something minor, but I would appreciate any assistance that could be provided. I may be going about this all wrong in the first place.

Upvotes: 2

Views: 4754

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this instead:

SELECT i.indid,    i.indicator, it.indtype, 
       p.provider, r.report,    a.actor 
FROM actor a 
INNER JOIN indicator i ON a.actorid    = i.actorid
INNER JOIN indtype  it ON i.indtypeid  =  it.indtypeid
INNER JOIN report   r  ON i.reportid   = r.reportid 
INNER JOIN provider  p ON r.providerid = p.providerid 
WHERE 1 = 1
  AND ($indicator IS NULL OR i.indicator LIKE '%$indicator%')
  AND ($indtypeid IS NULL OR i.indtypeid = $indtypeid);

So if you pass a $indicator = NULL, then the first condition AND ($indicator IS NULL OR i.indicator LIKE '%$indicator%') will be ignored since it will resolve to True, and the same thing for the second condition.

I've removed other Where condition and replace them with JOINs, and for WHERE 1 = 1 to make the query work fine in case you pass the two variables $indicator and $indtypeid with NULL values for each, in this case it will return all results since 1 = 1 always true.

Upvotes: 6

Related Questions