Page 1 of 1

Generate queries in php by month

Posted: Thu May 05, 2011 7:45 pm
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?

Re: Generate queries in php by month

Posted: Fri May 06, 2011 11:33 am
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".

Re: Generate queries in php by month

Posted: Fri May 06, 2011 11:50 am
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);
?>

Re: Generate queries in php by month

Posted: Fri May 06, 2011 12:10 pm
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

Re: Generate queries in php by month

Posted: Fri May 06, 2011 4:04 pm
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.

Re: Generate queries in php by month

Posted: Fri May 06, 2011 5:33 pm
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>"; 

?>

Re: Generate queries in php by month

Posted: Fri May 06, 2011 6:52 pm
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

Re: Generate queries in php by month

Posted: Fri May 06, 2011 7:54 pm
by mikosiko
@cjkeane: you are grouping only by year... if you want counts for year and quarters you must group based on that.

Re: Generate queries in php by month

Posted: Sun May 08, 2011 3:54 pm
by cjkeane
i grouped by year and by quarters and it worked!