Past year queries

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!

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Past year queries

Post 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!
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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 :)
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Seems like a good solution.

However, when I run the code it only prints Resource id#4
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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 :)
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

it'll only include moths which have totals.. august doesn't have any yet, and jan/feb 2006 where also 0 (zero) :)
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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)?
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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]
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

glad to hear it .. really though, reading the documentation doesn't take you as long as you think and it's recommended :)
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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.
Post Reply