Allowing Optional Parameters for MySQL Query

[chrissanders]

Hey folks,

 

I'm working on a fairly simple app where folks can input some data into a database, and then search on it based upon different criteria. Currently, the last thing I have left to do is to create the search page. This is a bit trickier than what I'm used to however, because there are multiple option criteria that can be used for the search. On this search page, there are two fields. Both fields are optional. I'm trying to start crafting my sql query so that it will work given the proper variables but I'm having trouble. With this, I'd want the user to be able to search for any indicator, or any indicator that is also a particular indicator type ID, or just search and list all of the indicator types. Basically just an advanced single search that will return any combination of the search key terms. Eventually, I will be adding more fields to this.

 

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.

 

[mikosiko]

First... you should be using explicit JOIN's instead of that implicit notation ... answering your question... you can try this:

 

....
AND indicator.indtypeid = IF( ($indtypeid='*'), indicator.indtypeid, $indtypeid);

 

IF(<condition>, <value if true>, <value if false>)