Jump to content

Recommended Posts

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.

 

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.