PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
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):
$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'"));
I would like these queries to be ran through a loop like this:
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
}
}
Any help towards accomplishing my goal would be great!
$everything = mysql_query("SELECT count(*) as ordercount, LEFT(DATE(date_ordered), 7) as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
print_r($everything);
$everything = mysql_query("SELECT count(*) as ordercount, LEFT(DATE(date_ordered), 7) as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
while ($row = mysql_fetch_assoc($everything)) {
print_r($row);
}
<?php
include("connect.php");
$result = mysql_query("SELECT count(*) as ordercount, LEFT(DATE(date_ordered), 7) as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
while($row = mysql_fetch_assoc($result)) {
echo $row["yearmonth"] . " = " . $row["ordercount"] . "<br>";
}
?>
<?php
include("connect.php");
$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered, '%M %Y')) as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
while($row = mysql_fetch_assoc($result)) {
echo $row["yearmonth"] . " = " . $row["ordercount"] . "<br>";
}
?>
as for the empty months, if they aren't in the table (which they are not) then you can't output them.. as they are not there.. you'll ahve to inject the jan/feb 2006 into the output manually.. not like it can change is it? seeing as it's back in time.. as for august.. I have a feeling that y the time you code it up, and run the output again, you'll have a "case" there and it will be output anyways.
end of the day - you can't get something from a database that isn't there.
Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_c' at line 1
<?php
include("connect.php");
$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered, '%M %Y') as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
while($row = mysql_fetch_assoc($result)) {
echo $row["yearmonth"] . " = " . $row["ordercount"] . "<br>";
}
?>
duplicate bracket in there, the problems that come with typing sql straight into a forum from the top of your head.
bailing out of this thread now, you need to put some effort in yourself before asking others for help, if you can't do it, don't sell yourself as being able to, if you want others to do it for you, pay them to do it.
I never said I was an professional programmer. I don't even do it for a living. It is just a good friend of mine that I help out, and using this forum has helped me solved my questions. However, I am not claiming to be a professional PHP programmer.