Page 1 of 1

Past year queries

Posted: Tue Jul 31, 2007 1:11 pm
by icesolid
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):

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'"));
I would like these queries to be ran through a loop like this:

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
    }
}
Any help towards accomplishing my goal would be great!

Posted: Tue Jul 31, 2007 6:24 pm
by nathanr
you can do all that with 1 query mate..

Code: Select all

$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);
hope that helps :)

Posted: Wed Aug 01, 2007 9:57 am
by icesolid
Seems like a good solution.

However, when I run the code it only prints Resource id#4

Posted: Wed Aug 01, 2007 10:04 am
by nathanr
many apologies!

Code: Select all

$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);
}
there you go :)

Posted: Wed Aug 01, 2007 10:10 am
by icesolid
This query also changes depending on leap years because of its use of MySQL to select date ranges, correct?

One other thing, I am using this code below:

Code: Select all

<?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>";
}
?>
I am getting this result:

2006-03 = 9
2006-04 = 12
2006-05 = 58
2006-06 = 166
2006-07 = 155
2006-08 = 243
2006-09 = 191
2006-10 = 208
2006-11 = 199
2006-12 = 358
2007-01 = 367
2007-02 = 364
2007-03 = 423
2007-04 = 474
2007-05 = 438
2007-06 = 378
2007-07 = 380

It is not printing the January and February from 2006. It also is not printing August of 2007, it should because today is the 1st of August.

Posted: Wed Aug 01, 2007 10:18 am
by nathanr
it'll only include moths which have totals.. august doesn't have any yet, and jan/feb 2006 where also 0 (zero) :)

Posted: Wed Aug 01, 2007 10:21 am
by icesolid
True. What if I want to print out that there were 0 ordered?

It does change for leap years, right?

Any easy way to format dates from their current format to a different one (ex: 2006-03 to March 2006)?

Posted: Wed Aug 01, 2007 1:53 pm
by nathanr
nice attempt at doing the new thread mate.. I'm goign to help you a bit more here, although you really should read the manuals... 8O

this'll format the dates as you wish..

Code: Select all

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

+ for reference MySQL 5.0 Reference Manual :: 12 Functions and Operators :: 12.6 Date and Time Functions[/url]

Posted: Wed Aug 01, 2007 2:49 pm
by icesolid
I get an SQL error that says:

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

Posted: Wed Aug 01, 2007 2:54 pm
by nathanr

Code: Select all

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

Posted: Wed Aug 01, 2007 2:58 pm
by icesolid
Thank you for you help. Sorry for the troubles.

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.

Posted: Wed Aug 01, 2007 3:01 pm
by nathanr
glad to hear it .. really though, reading the documentation doesn't take you as long as you think and it's recommended :)

Posted: Wed Aug 01, 2007 3:05 pm
by icesolid
Next time I will dig into it further. I realize where the mistake was and I see now how stupid I am for not seeing it.