Help with date search...

[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

 

 

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

 

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 dunamic??

 

Thanx in advance

[Muddy_Funster]

I dont follow your current logic.  Why are you setting both year and month in the query for each of $month and $year? also, why does $year allow a search back untill 1970 and the month only go back to 2011?

[marcushjortsberg]

I'm sure you are right, my logic is wrong...

 

As I said, I have written it all manually year for year and month for month, and it works...

 

But hopefully both I and my site will for several years and I would like to have some simple code that makes my life easier:)

 

So what I want is a query that gets dates within a year and month, but I can't get the logic right...

 

I have temporarly solved the problem by giving the user the opportunity to choose startyear, endyear, startmonth and endmonth (what I have to do if I choose to stick with my temorarly coding is to validate so that the user don't choose startyear and startmonth that is less than endyear and endmonth...don't want to do that...).

 

So if you have any suggestions on how to solve my problem I would be very greatfull!

 

Thanx for responding!

[Muddy_Funster]

I have knnocked up a little function that does what I THINK you want.  I havn't tested it, so don't go getting excited or anything

You would fit this into your code by calling it when you are building your main SELECT query.

<?php
function dateQuery($m, $y, $dateField){
$m = stripslashes(trim($m));
$y = stripslashes(trim($y));
if ($m == '' || $m = '0'){
  $m = '01';
  $m_all = TRUE;
}
if (($m != '0' && strlen($m == '1'))) {
$m = '0'.$m;
}
if (($y == '') || ($y == '0')){
  $y = '1970';
  $y_all = TRUE;
}
if(!isset($m_all)){
  $m_qry = " MONTH($dateField) = $m ";
}
else {
  $m_qry = "$m-01 "; 
}
if(!isset($y_all){
  $y_qry = " YEAR($dateField) = $y ";
}
else {
	$y_qry = "$y-";
}
if (isset($y_all) && isset($m_all){
  $qry = "WHERE $dateField BETWEEN $y_qry$m_qry AND CURRENT_TIMESTAMP";
}
elseif (isset($y_all) && !isset($m_all)) {
  $qry = "WHERE((YEAR($dateField) BETWEEN $y_qry AND YEAR(CURRENT_TIMESTAMP)) AND ($m_qry))";
}
elseif (isset($m_all) && !isset($y_all)) {
  $qry = "WHERE((MONTH($dateField) BETWEEN $m_qry AND 12) AND ($y_qry))"; 	
}
else {
	$qry = "WHERE $y_qry AND $m_qry";
}
return $qry;   
}

$mainQuery = "SELECT field1, field2, field3 FROM table "; //change to your actual select & from query information
$whereDate= dateQuery($month, $year, "date_field_name");  //call the function to build the date check sending in $month, $year and the field name in the table of the the date field.
$mainQuery = $mainQuery.$whereDate;

$result = mysql_query($mainQuery);
while...// rest of your code as normal