chrissanders Posted April 17, 2012 Share Posted April 17, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/261118-allowing-optional-parameters-for-mysql-query/ Share on other sites More sharing options...
mikosiko Posted April 17, 2012 Share Posted April 17, 2012 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>) Quote Link to comment https://forums.phpfreaks.com/topic/261118-allowing-optional-parameters-for-mysql-query/#findComment-1338190 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.