mysql query date range by drop down

[Tinman]

Hi all,
Well I was doing fine until I decided I need a date range rather than just selecting a month and year.

I have 4 drop down menus.
beginning "month" + "year" and End "month" and "year"

my code for this:
//Get beginning report date
$firstrepyear=$_POST['firstreport_year'];
if (isset($firstrepyear) && $firstrepyear !=="") {
$firstpostyear = $firstrepyear;
}
else $firstpostyear = "2010";

$firstrepmonth=$_POST['firstreport_month'];
if (isset($firstrepmonth) && $firstrepmonth !=="") {
$firstpostmonth = $firstrepmonth;
}
else $firstpostmonth = "";

//Get end of report date
$secondrepyear=$_POST['secondreport_year'];
if (isset($secondrepyear) && $secondrepyear !=="") {
$secondpostyear = $secondrepyear;
}
else $secondpostyear = "2010";

$secondrepmonth=$_POST['secondreport_month'];
if (isset($secondrepmonth) && $secondrepmonth !=="") {
$secondpostmonth = $secondrepmonth;
}
else $secondpostmonth = "";


I am stuck on the query.
For a single month and year selection, I got this far:

$rep_e_query = ("select category, SUM(amount) as Etotal from expenses where UserID='$owner_rows' and year(date_purchased)='$postyear' and month(date_purchased)='$postmonth' GROUP BY category")or die(mysql_error());
$rep_e_result = mysql_query($rep_e_query)or die(mysql_error());

I don't know how to implement a date/between statement here.
I think I could do it if it was a static date like between 201005 and 201006 but I am confused in implementing the dynamic date into the existing query.


Any help would be appreciated,
thanks
Tin

[rocknbil]

I'll give you the cliff notes on how I do it. :-)

You have a month and year only, right? I usually have a day too, but if you eliminate that you can make some assumptions.

So you write some function that does this:

$start_list = date_list('start');
$end_list = date_list('end');

And this gives you two strings, each containing two select lists, that are named like so:

<select name="start_month" id="start_month">
...
<select name="start_year" id="start_year">
...
<select name="end_month" id="end_month">
...
<select name="end_year" id="end_year">

OK? So on input, you do this:

$start_mm = $_POST['start_month'];
$start_yy = $_POST['start_year'];
$end_mm = $_POST['end_month'];
$end_yy = $_POST['end_year'];

Since you have no day, you decide what assumptions you want to make. I'd say 1 and 31 for start and end (never mind about short months, it will still work.) So

$start="$start_yy-$start_month-01";
$end="$end_yy-$end_month-31";

So now you have valid mySQL dates. And that's really the key.

$query = "select category, SUM(amount) as Etotal from expenses where UserID='$owner_rows' and date_purchased >= '$start' and date_purchased <= '$end' group by category";

$result = mysql_query($query);

Don't make the mistake of thinking you need 'or', any date less than $start will still be true for less than $end, and vice versa. You want 'and' for the range.

I'm not sure, but you *may not* need the group by for a simple sum(), but you might. At any rate that should work.

[Tinman]

Thank you Rocknbil,
I took a bit of a break from the php/MySQL,

I understood everything you wrote, except for this part:
"So you write some function that does this:

$start_list = date_list('start');
$end_list = date_list('end');

And this gives you two strings......."

This confused me. Hmm, date_list isn't a "built in" PHP function.
So, I'm guessing you suggested "function" so I can call it whenever I want?

Can you please clarify the above since I do want to learn.

I did understand the rest of the code, it is implemented and working.
Thank you.
I knew it was a problem of Mysql dates like you said.
I did try some code with date/between and converted the various variables with mktime,
but after doing many hours of reading, I didn't feel this would yield the results I needed.
Everything pointed me to the <= >= for the query.
The light went on when you wrote:
$start="$start_yy-$start_month-01";
$end="$end_yy-$end_month-31";

Thank you,
and yes, you bil, do indeed rock!
Cheers,
tin

[rocknbil]

So, I'm guessing you suggested "function" so I can call it whenever I want?

Right . . . you had that part already, but it's a "good idea" to build a class for date functions or at least a function so it can be reused throughout your program(s).