2. PHPRunner General questions
  3. [SOLVED]  Dynamic BETWEEN clause in a query
This topic is locked
[SOLVED]

 Dynamic BETWEEN clause in a query

 		12/8/2006 11:34:12 PM
PHPRunner General questions
L
 larsonsc author

Does anyone know if it is possible in MySQL to create a dynamic portion of a query that is determined by another value? My research into this so far has not been very helpful, so I thought I'd ask here.
I am trying to build a query that generates a quarterly report. In the query I have right now, I use the following WHERE clause to set the date range:

WHERE adoption_records.Adoption_Date BETWEEN '2007-01-01' AND '2007-03-31'


What I would like to do is have the query evaluate CURDATE() and based on that value, choose what values are inserted into the BETWEEN statement thus making the query nice and dynamic. Meaning I'm kind of lazy about going in and making manual adjustments to my code for things like date ranges.
Of course in an ideal world, I would be able to have it do some fancy math so that it knows how to select the right year as well. I.E. - If CURDATE() falls in Q1 (Jan 1 - Mar 31) then the query chooses Q4 ranges from the previous year.
Is this possible or am I better off just developing 4 independent queries? Thanks for any help anyone can offer.
--Shawn
T
 		thesofa 12/9/2006

this might work, its an old trick but it may fool them, moriarty!
All you have to do is determine the last and the penultimate quarter days from the current date.
with some custom code

$todays_date=now();
looking up the modulus function from here will help you work out how its done
using the priciples from the code snippet published by Scott Hurring in here find the monday and friday of the week to determine the correct start and finish dates for the quarter.

if the month from $todays_date is 1, 2 or 3 , year date is (year from $todays_date-1)

do similar to find the months to be used, this will be a case select type of code

build up the query dates of

$begin_date and

$end_date

from the data you have just worked out

change the query from

WHERE adoption_records.Adoption_Date BETWEEN '2007-01-01' AND '2007-03-31'

to

WHERE adoption_records.Adoption_Date BETWEEN '$begin_date' AND '$end_date'



This will need more coding but i think it will point you the way.

If not, I have a snippet that will find the next Saturday in the calendar for me so it will be changeable to suit your needs.

HTH
L
 		larsonsc author 12/9/2006

Sofa,
Thanks for the response. I'll have to get out my PHP books and figure out how to code that and then also figure out where to put the code once I do get it figured out.
I was considering editing the *_variables.php to include an if/else which would assign the appropriate BETWEEN clause to a variable then insert the variable into the SQL string, btu I'm not sure that will work since I'm not sure how smarty will respond to that method.
Anyway, I appreciate the push in the right direction, and I'll see what I can do to figure this out.
--Shawn
T
 		thesofa 12/10/2006

Hi

sorry I cannot give you full code, I have not used 3.1 yet so I have no idea how smarty works.
L
 		larsonsc author 12/10/2006

No problem Sofa. Thanks for the suggestion.
To be perfectly honest, after spending several hours over several days trying to make the report "smart", I finally decided that it wasn't that important and just changed the link for the report to go straight to the search page which only allows searching by date. This way the user can just pick the quarter start and end dates, and get the report they want. I'm not saying I won't keep trying to make the query dynamic...but I had to get the application published by this morning so I went with what I could make work last night. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=13737&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
--Shawn
T
 		thesofa 12/12/2006

OK, I think I may have the answer for you, you will have to test it by changing the dates in your PC to see if the year changes work.

The code that follows has not been production tested, you will need to test it.

If you make any mods, please post it back here for us all.
The echo statements are for testing.

Call the file wibble.php and copy it to your webserver, then open the file in the browser, you should just see the month and year components of the current date, the number of months past the quarter month and then the end and start dates, ready with the quotes around to allow them to be used in a sql statement.

<?php

//get month and year from date

$month=date("m");

$year=date("Y");

echo $month;

echo"<BR>";

echo $year;

//find no of months past the 3 month gaps

$sep=$month%3;

echo"<BR>";

echo $sep;

//This bit takes it back 3 months if date falls on a quarterly month

if($sep==0)

{

$month=$month-3;

}

//if month is 0, Jan or Feb, go to previous year and set last month = December

if ($month<3){

$year=$year-1;

$end_month=12;

}

else

{

//change the end month to the prvious quarterly one

$end_month=$month-$sep;

}

$start_month=$end_month-2;

//Thirty days hath september, April June and November

//All the rest have 31, cept Feb, but Feb <>quarterly month!! Hurrah

if($end_month==3||$end_month==12)

{

$last_day=31;

}

else

{

$last_day=30;

}

//Make up the start and end dates

$end_date="'". date('Y-m-d H:i:s', mktime(0,0,0,$end_month, $last_day, $year ))."'";

$start_date="'". date('Y-m-d H:i:s', mktime(0,0,0,$start_month, "1", $year ))."'";

echo"<BR>";

echo $end_date;

echo"<BR>";

echo $start_date;

?>


The output on my screen is

12

2006

0

'2006-09-30 00:00:00'

'2006-07-01 00:00:00'



HTH