IF and OR in a query

**Ewoud Dronkert** · Jul 17 '05, 01:29 PM

Re: IF and OR in a query

On Sun, 05 Jun 2005 04:11:25 -0700, Chris wrote:[color=blue]
> 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?[/color]

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

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

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

**Chris** · Jul 17 '05, 01:29 PM

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)

On Sun, 05 Jun 2005 04:11:25 -0700, Chris <coverland914 @ yahoo.com>
wrote:
[color=blue]
>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?[/color]

**Jerry Stuckle** · Jul 17 '05, 01:29 PM

Re: IF and OR in a query

(Response limited to comp.lang.php)

Chris wrote:[color=blue]
> 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?[/color]

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.
jstucklex@attgl obal.net
=============== ===

**Joseph Melnick** · Jul 17 '05, 01:29 PM

Re: IF and OR in a query

Hello Chris,

Use: INSTR(columnnam e,'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

http://www.jphp.com

"Chris" <coverland914 @ yahoo.com> wrote in message
news:loo5a1p0gc 91h8i0uobbpir6k 87pbij07f@4ax.c om...[color=blue]
> Another way of 'saying' that might be
>
> IF jul THEN INCLUDE junjul AND julaug
> (in the resultant query)
>
> On Sun, 05 Jun 2005 04:11:25 -0700, Chris <coverland914 @ yahoo.com>
> wrote:
>[color=green]
>>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?[/color]
>[/color]

IF and OR in a query

IF and OR in a query

Comment

Comment

Comment

Comment