Past year queries
Posted: Tue Jul 31, 2007 1:11 pm
I want to find what the years between 2006 till the current year are and if any were leap years, and if so which one.
Basically I need to build SQL queries for each month in each year since 2006 and if its a leap year obviously the dates being selected in the query need to be different for the leap year.
Current code for finding the queries (I have to build a set of them for each month for each year. I would like this to be more dynamic):
I would like these queries to be ran through a loop like this:
Any help towards accomplishing my goal would be great!
Basically I need to build SQL queries for each month in each year since 2006 and if its a leap year obviously the dates being selected in the query need to be different for the leap year.
Current code for finding the queries (I have to build a set of them for each month for each year. I would like this to be more dynamic):
Code: Select all
$january06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-01-01' AND '2006-01-31'"));
$february06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-02-01' AND '2006-02-28'"));
$march06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-03-01' AND '2006-03-31'"));
$april06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-04-01' AND '2006-04-30'"));
$may06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-05-01' AND '2006-05-31'"));
$june06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-06-01' AND '2006-06-30'"));
$july06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-07-01' AND '2006-07-31'"));
$august06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-08-01' AND '2006-08-31'"));
$september06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-09-01' AND '2006-09-30'"));
$october06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-10-01' AND '2006-10-31'"));
$november06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-11-01' AND '2006-11-30'"));
$december06 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2006-12-01' AND '2006-12-31'"));
$january07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-01-01' AND '2007-01-31'"));
$february07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-02-01' AND '2007-02-28'"));
$march07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-03-01' AND '2007-03-31'"));
$april07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-04-01' AND '2007-04-30'"));
$may07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-05-01' AND '2007-05-31'"));
$june07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-06-01' AND '2007-06-30'"));
$july07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-07-01' AND '2007-07-31'"));
$august07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-08-01' AND '2007-08-31'"));
$september07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-09-01' AND '2007-09-30'"));
$october07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-10-01' AND '2007-10-31'"));
$november07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-11-01' AND '2007-11-30'"));
$december07 = mysql_num_rows(mysql_query("SELECT * FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND date_ordered BETWEEN '2007-12-01' AND '2007-12-31'"));Code: Select all
for(each_year_since_2006_including_current_year) {
// leap year detection here
if(leap_year) {
// generate a variable based on the year and month
ex: $january.$year = //query using year and dates of that month, different days in feb. because its a leap year
$february.$year
// and so on
} else {
// generate a variable based on the year and month
ex: $january.$year = //query using year and dates of that month
$february.$year
// and so on
}
}