IF and OR in a query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Having a little trouble returning the result I'm looking for in a
query and thought I'd ask a question here.

I have an input screen with a pull down menu to capture vacation
activity within a given month as well as capture overlap onto another
month.  For example, if you're taking vacation between June 25 and
July 4, the input would be 'junjul'  if the days off were July 4
through 10, the input would be 'jul' and if the days off happened to
be July 25 through August 5, the input would fall under 'julaug' with
the idea being that the query captures any vacation for the month of
July but also sends back info for any overlapping month vacation.
Also, if you did a query for the month of June or August, that
overlapping vacation would show as well.  I did this very effectively
in Access once and am trying duplicate it with PHP.

So a question becomes, IF jul (is selected in the query for the month
of July) THEN return any data for junjul AND julaug if they were used
on the input form and have that data returned under a July query.

Any thoughts?

Re: IF and OR in a query

Another way of 'saying' that might be

IF jul THEN INCLUDE junjul AND julaug
(in the resultant query)


Quoted text here. Click to load it

Re: IF and OR in a query

Hello Chris,

Use:  INSTR(columnname,'jul') > 0

This will evaluate to a positive integer when 'jul' is in the search column
or zero when it is not found.

Joseph Melnick
JM Web Consultants

Quoted text here. Click to load it

Re: IF and OR in a query

On Sun, 05 Jun 2005 04:11:25 -0700, Chris wrote:
Quoted text here. Click to load it

Assuming you will sensibly have 2 date fields in your table:

$month = 7;  // July
$sql = "SELECT id FROM planning WHERE MONTH(startdate)=$month OR

Firefox Web Browser - Rediscover the web - http://getffox.com /
Thunderbird E-mail and Newsgroups - http://gettbird.com /

Re: IF and OR in a query

(Response limited to comp.lang.php)

Chris wrote:
Quoted text here. Click to load it

I think using the junjul/julaug/whatever will complicate matters.  For
instance 0 my wife's ex-AA just had a baby and is taking 3 months off.
And my wife has > 2 months of vacation signed up.  If you use
junjul/julaug, what about the person who takes an extended vacation such
as May 31 - August 2?  Or even May 30 - Sept. 1?

To resolve everything, use a separate start date and end data,  Then,
you need these tests:

   1. start date >= 07/01 and start data <= 07/31 (or < 08/01 which
might be easier), or
   2. end data - same as above, or
   3. start date < 07/01 and end date > 07/31 (or >= 08/01)

Easily handled in SQL statements or date functions.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline