Page 1 of 1

Help with date search...

Posted: Sun Jan 08, 2012 1:49 pm
by marcushjortsberg
I have a page where the user can search with dropdowns. Two of these are lists for year and month.

Month list is from 0-12, where 0 is all months, 01 is for january, 02 for february, and so on...

The year list have 0, 2011 and 2012 (so far, will expand as times go) where 0 is all years...

When I have made my query I have had

Code: Select all

if($month == ''){$month = "";}
			if($month == '0'){$month = " AND mytable.datum >= '2011-01-01' AND mytable.datum < '2012-01-01'";}
			if($month == '1'){$month = " AND mytable.datum >= '2011-01-01' AND mytable.datum < '2011-02-01'";}
...
...
and

Code: Select all

if($year == '0'){$year = " AND mytable.datum >= '1970-01-01' AND mytable.datum < '2050-01-01'";}
			if($year == '2010'){$year = " AND mytable.datum >= '2010-01-01' AND mytable.datum < '2011-01-01'";}
...
...

manually coding...

Now I want it to be more dynamic:)

But I am stuck :-[

Anybody have any ideas on how to make my query more dynamic??

Thanx in advance :D

Re: Help with date search...

Posted: Mon Jan 09, 2012 1:42 pm
by tr0gd0rr
Try constructing the dates dynamically. And you can use BETWEEN. Be sure to escape the values to prevent SQL injection.

Code: Select all

$from = mysql_real_escape_string("$fromYear-$fromMonth-01");
$daysInMonth = myFunctionToGetDaysInMonth($toYear, $toMonth);
$to = mysql_real_escape_string("$toYear-$toMonth-$daysInMonth 23:59:59");
$clause = "AND my_table.datum BETWEEN $from AND $to";