Generate queries in php by month

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
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Generate queries in php by month

Post by cjkeane »

i'm trying to create a query to list all records in the database by year and by month, but its only listing a couple of records. i know for a fact that there are over 100 records. any ideas why my query doesnt find all records? every record has a daterecorded date.

Code: Select all

<?php>
query="SELECT DATE_FORMAT(DateRecorded, '%Y-%m') as Month, COUNT(DATE_FORMAT(DateRecorded, '%Y-%m')) AS Count, ROUND( 100 * COUNT(DATE_FORMAT(DateRecorded, '%Y-%m')) / Count2, 2 ) AS Percentage, 
(CURDATE( ) - DateRecorded) AS AgeInDays, (DateClosed - DateRecorded) AS ResTime, 
sum(IF(Status='Closed',1,0)) As Resolved ,sum(if(Status='Open',1,0)) As Unresolved ,
sum(if(Status='Pending',1,0)) As Pending 
FROM records, (
SELECT COUNT(DATE_FORMAT(DateRecorded, '%Y-%m')) AS Count2
FROM records
) AS c
GROUP BY Count2 ASC 
WITH ROLLUP";

?>

the output needs to be listed by year and month, like so:
2011-05
2011-04
2011-03
etc

any ideas on how to accomplish this?
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Generate queries in php by month

Post by incubi »

Your query may be finding them all but you may not be displaying them right. You've shown us the query but you need to show how you are displaying the results that only give "a couple of records".
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Generate queries in php by month

Post by cjkeane »

i've figured it out. i'm not sure why it wasn't displaying right.

Code: Select all

<?php
 $string = "
SELECT DATE_FORMAT(DateRecorded, '%Y-%m') AS Month, 
COUNT(DATE_FORMAT(DateRecorded, '%Y-%m')) AS Count, 
ROUND( 100 * COUNT(DATE_FORMAT(DateRecorded, '%Y-%m')) / Count2, 2 ) AS Percentage, 
COUNT(*) AS NumberReceived,
sum(IF(Status='Closed',1,0)) As Resolved ,
sum(if(Status='Open',1,0)) As Unresolved ,
sum(if(Status='Pending',1,0)) As Pending  FROM records, (
SELECT COUNT(DATE_FORMAT(DateRecorded, '%Y-%m')) AS Count2
FROM records
) AS c
GROUP BY Month DESC
WITH ROLLUP"; 
    $query = mysql_query($string) or die (mysql_error());
	$num_rows = mysql_num_rows($query);
?>
now i have another issue.

on a detail page, i'm running into a sql error as it cant find the column MONTH i'm referring to. Any ideas?

Code: Select all

<?php
$Month = 	$_GET['Month'];
 $string = "SELECT DATE_FORMAT(DateRecorded, '%Y-%m') as Month, 
	Status, MegaCaseNumber, DateRecorded, DateClosed, 
	(CURDATE() - DateRecorded) AS AgeInDays,
	(DateClosed - DateRecorded) AS ResTime
	FROM records
	WHERE Month = '$Month'"; 
    $query = mysql_query($string) or die (mysql_error());
	$num_rows = mysql_num_rows($query);
?>
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Generate queries in php by month

Post by incubi »

I don't think you can use Month in the WERE clause because its not a real column in the DB.
You should get the error Unknown column 'Month' in 'where clause'
Lee
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Generate queries in php by month

Post by McInfo »

WHERE is applied while choosing which rows to use. HAVING is applied later to filter the selected rows. So, to filter rows based on an alias (like Month), use HAVING. Otherwise, do the operation (like DATE_FORMAT...) again in the WHERE clause.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Generate queries in php by month

Post by cjkeane »

i realized why the query didnt run.. and like you say, month really wasn't a column in the table so i ended up using date format.

one last question: i'm now trying to get dates sorted by quarter and even though the query runs successfully, the results aren't quite right.

it only displays two rows with totals and not multiple rows based on the quarter:
2011-Q1 etc
2010-Q1 etc

Code: Select all

<?php
 $string = "SELECT DATE_FORMAT(DateRecorded, '%Y') AS Year, QUARTER(DateRecorded) as Quarter,
COUNT(DATE_FORMAT(DateRecorded, '%Y')) AS Count, 
ROUND( 100 * COUNT(DATE_FORMAT(DateRecorded, '%Y')) / Count2, 2 ) AS Percentage, 
COUNT(*) AS NumberReceived,
sum(IF(Status='Closed',1,0)) As Resolved ,
sum(if(Status='Open',1,0)) As Unresolved ,
sum(if(Status='Pending',1,0)) As Pending  FROM records, (
SELECT COUNT(DATE_FORMAT(DateRecorded, '%Y')) AS Count2
FROM records
) AS c
GROUP BY Year DESC
WITH ROLLUP"; 
    $query = mysql_query($string) or die (mysql_error());
	$num_rows = mysql_num_rows($query);
$result = mysql_fetch_array($query);
if($result==true)
{ 
do 
	{ 
	
	echo "<tr>";
	echo '<td><a href="pp_byquarterdetails.php?Quarter='.$result['Year']. '' .$result['Quarter'].'" > '.$result['Year']. '-Q' .$result['Quarter'].'</a></td>';	
	echo '<td nowrap>' . $result['Percentage'] . '</td>';
	echo '<td nowrap>' . $result['Count'] . '</td>';
	echo '<td nowrap>' . $result['Resolved'] . '</td>';
	echo '<td nowrap>' . $result['Unresolved'] . '</td>';
	echo '<td nowrap>' . $result['Pending'] . '</td>';

    }
	while($result = mysql_fetch_array($query));
}                
     // close table>
     echo "</table><hr>"; 

?>
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Generate queries in php by month

Post by incubi »

I'm on a PC where I can't test the code now but I think because you are grouping by year you will only see each year in the DB. I don't know if you can group by Quarter I've never tried it. You may need to do two queries to get what you need or maybe a view query first then a final query off that. I use Views a LOT to save time. McInfo may have some ideas.

incubi
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Generate queries in php by month

Post by mikosiko »

@cjkeane: you are grouping only by year... if you want counts for year and quarters you must group based on that.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Generate queries in php by month

Post by cjkeane »

i grouped by year and by quarters and it worked!
Post Reply