Solved

GROUP BY For reports

Posted on 2014-02-11
7
212 Views
Last Modified: 2014-02-12
Hi all,

seems like I hit another brick wall and hope someone with great knowledge and expertise can safe me once again.

I have a mysql database that contains data captured for a car park. Its capturing data such as arrival time and departure time in unixtime, salesid and parking cost.
I need to create a reports page from that database, that can show me income by user defined year range, such as 2010, 2011 etc.

I would like to display this data in a human readable way, such as:

 
YEAR: 2010
MONTH: January
01 Feb,2010 || ¿3050
02 Feb,2010 || $4220
03 Feb,2010 || $1160
Etc for remaining entries

MONTH: February 
01 Mar,2010 || ¿690
02 Mar,2010 || $3140
03 Mar,2010 || $2890
Etc for remaining entries
Etc for remaining Months

YEAR: 2011
MONTH: January
01 Jan,2011 || ¿2770
02 Mar,2011 || $1200
03 Mar,2011 || $2390
04 Mar,2011 || $890
Etc for remaining entries
Etc for remaining Months

Open in new window


and so forth. I ham currently using this the following query to accomplish this, but fails badly. The code I am using is:

$yearSQL = "SELECT checkout_time  FROM shcp_salesitems  
WHERE
YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
GROUP BY YEAR(FROM_UNIXTIME(checkout_time)) 
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC";

$yearRLT = mysql_query($yearSQL) or die("Error: ".mysql_error());
$yearCOUNT = mysql_num_rows($yearRLT);
	
while($yearROW = mysql_fetch_array($yearRLT)){
								
	$year = date('Y',$yearROW['checkout_time']);
	
	echo('<br /><pre>YEAR: '.$year.'</pre>');
	
	
		$monthSQL =
		"SELECT checkout_time  FROM shcp_salesitems 
		WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string($year) . "' 
		GROUP BY MONTH(FROM_UNIXTIME(checkout_time))
		ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC";
		
		$monthRLT = mysql_query($monthSQL) or die("Error: ".mysql_error());
		$monthCOUNT = mysql_num_rows($monthRLT);

			while($monthROW = mysql_fetch_array($monthRLT)){

				$month = date('m',$monthROW['checkout_time']);											
					
					echo('<pre>MONTH: '.date('F',$monthROW['checkout_time']).'</pre>');
						$daySQL =
						"SELECT checkout_time FROM shcp_salesitems 
						WHERE MONTH(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string($month) . "' 
						GROUP BY MONTH(FROM_UNIXTIME(checkout_time))";
						$dayRLT = mysql_query($daySQL) or die("Error: ".mysql_error());
						$dayCOUNT = mysql_num_rows($dayRLT);
						
							while($dayROW = mysql_fetch_array($dayRLT)){
									
									$day = date('d',$dayROW['checkout_time']);
										$daySQL =
										"SELECT checkout_time,salesid, payed_amount FROM shcp_salesitems 
										WHERE DAY(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string($day) . "'";
										$dayRLT = mysql_query($daySQL) or die("Error: ".mysql_error());
										$dayCOUNT = mysql_num_rows($dayRLT);
											while($dayROW = mysql_fetch_array($dayRLT)){
												
												echo('<pre>'.date('d M,Y',$dayROW['checkout_time']).' || &#3647;'.$dayROW['payed_amount'].'</pre>');
										
												}
										}
							}
					}

Open in new window


The output I am getting from my database is:

YEAR: 2014
MONTH: February
12 Feb,2014 || ¿150
MONTH: January
10 Jan,2014 || ¿1000

YEAR: 2013
MONTH: June
28 Jun,2013 || ¿677

YEAR: 2012
MONTH: August
21 Aug,2012 || ¿500

YEAR: 2011
MONTH: December
06 Dec,2011 || ¿130

YEAR: 2010
MONTH: October
05 Oct,2010 || ¿1500

Open in new window


The database contains 15 entries, so I am missing 9 entries somehow. I believe I am somehow creating to many GROUP BY and while loops somehow.
shcp-salesitems.sql
0
Comment
Question by:slickip
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39852616
I don't know why you are putting all those loops, you can do this in a single query + a bit of php, which looks like this (not tested for typose ...)
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, checkout_time
, payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
 checkout_time ";

$results = mysql_query($sql) or die("Error: ".mysql_error());


$prev_year = 0;
$prev_month = 0;
	
while($record = mysql_fetch_array($results))
{
								
	$year = $record["year"];
	$month = $record["month"];
        $time = date('d',$record['checkout_time']);
        $amount = $record["payed_amount"];

        if ($year != $prev_year)
 	{
		print("<br /><pre>YEAR: $year</pre>");
		$prev_year = $year;
                $prev_month = 0;
	}

     	if ($month != $prev_month)
 	{
		print("<pre>MONTH: $month</pre>");
       		$month = $prev_month;
	}
	

	print("<pre>$time&#3647;$amount</pre>");
}
                                  

Open in new window

0
 

Author Comment

by:slickip
ID: 39853221
Hi Guy,

Thanks for the reply. Running the code doesn't give me to output format I am really after:
YEAR: 2014
MONTH: 2
12¿10
MONTH: 2
12¿10
MONTH: 2
12¿150
MONTH: 1
13¿2000
MONTH: 1
10¿1000

YEAR: 2013
MONTH: 6
29¿1200
MONTH: 6
28¿677

YEAR: 2012
MONTH: 8
23¿900
MONTH: 8
22¿10
MONTH: 8
21¿500

YEAR: 2011
MONTH: 12
07¿500
MONTH: 12
07¿1000
MONTH: 12
07¿10
MONTH: 12
06¿130

YEAR: 2010
MONTH: 10
15¿230
MONTH: 10
08¿1000
MONTH: 10
05¿1500

Open in new window


The formatting should look like this and I think your supplied code doesn't produce that.
Need this:
YEAR: 2010
MONTH: January
01 Jan, 2010 || ¿3050
02 Jan, 2010 || ¿4220
03 Jan, 2010 || ¿1160
Etc for remaining entries

MONTH: February 
01 Feb, 2010 || ¿690
02 Feb, 2010 || ¿3140
03 Feb, 2010 || ¿2890
Etc for remaining entries
Etc for remaining Months

YEAR: 2011
MONTH: January
01 Jan, 2011 || ¿2770
02 Jan, 2011 || ¿1200
03 Jan, 2011 || ¿2390
04 Jan, 2011 || ¿890
Etc for remaining entries
Etc for remaining Months

Open in new window


Thank you for explaining an alternative way instead of using loops.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39853245
I see my error
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, MONTHNAME(FROM_UNIXTIME(checkout_time)) month_name
, FROM_UNIXTIME(checkout_time) checkout_time
, payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
 checkout_time ";

$results = mysql_query($sql) or die("Error: ".mysql_error());


$prev_year = 0;
$prev_month = 0;
	
while($record = mysql_fetch_array($results))
{
								
	$year = $record["year"];
	$month = $record["month"];
	$month_name = $record["month_name"];
        $time = date('d M, Y',$record['checkout_time']);
        $amount = $record["payed_amount"];

        if ($year != $prev_year)
 	{
		print("<br /><pre>YEAR: $year</pre>");
		$prev_year = $year;
                $prev_month = 0;
	}

     	if ($month != $prev_month)
 	{
		print("<pre>MONTH: $month_name</pre>");
       		$prev_month = $month;
	}
	

	print("<pre>$time  || &#3647;$amount</pre>");
}

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:slickip
ID: 39853367
Hi Guy,

Thank you. This is awesome and exactly what I am after. I was wondering if your supplied code could be modify to handle this output:

Currently we have:
YEAR: 2014
MONTH: February
12 Feb, 2014  || ¿10
12 Feb, 2014  || ¿10
12 Feb, 2014  || ¿150

Open in new window

Would it be possible to show only 1 entry of 12 Feb, 2014 but have a summary of this day? Like so:
YEAR: 2014
MONTH: February
12 Feb, 2014  || ¿170

Open in new window


Perhaps with a  "GROUP BY DAY(FROM_UNIXTIME(checkout_time))", but I doubt this.

Thanks.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39853401
yes, exactly that's what you need to do, with SUM() for the amount field:
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, MONTHNAME(FROM_UNIXTIME(checkout_time)) month_name
, FROM_UNIXTIME(checkout_time) checkout_time
, SUM(payed_amount) payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
GROUP BY YEAR(FROM_UNIXTIME(checkout_time)) 
, MONTH(FROM_UNIXTIME(checkout_time)) 
, MONTHNAME(FROM_UNIXTIME(checkout_time)) 
, FROM_UNIXTIME(checkout_time) 
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
, FROM_UNIXTIME(checkout_time) 
";

$results = mysql_query($sql) or die("Error: ".mysql_error());


$prev_year = 0;
$prev_month = 0;
	
while($record = mysql_fetch_array($results))
{
								
	$year = $record["year"];
	$month = $record["month"];
	$month_name = $record["month_name"];
        $time = date('d M, Y',$record['checkout_time']);
        $amount = $record["payed_amount"];

        if ($year != $prev_year)
 	{
		print("<br /><pre>YEAR: $year</pre>");
		$prev_year = $year;
                $prev_month = 0;
	}

     	if ($month != $prev_month)
 	{
		print("<pre>MONTH: $month_name</pre>");
       		$prev_month = $month;
	}
	

	print("<pre>$time  || &#3647;$amount</pre>");
}

Open in new window

0
 

Author Comment

by:slickip
ID: 39853586
Hi Guy,

Thank you very much! I have slightly modified your code to this:
$sql = "SELECT YEAR(FROM_UNIXTIME(checkout_time)) year
, MONTH(FROM_UNIXTIME(checkout_time)) month
, MONTHNAME(FROM_UNIXTIME(checkout_time)) month_name
, checkout_time
, SUM(payed_amount) payed_amount
FROM shcp_salesitems  
WHERE YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2010') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2011') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2012') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2013') . "' 
OR YEAR(FROM_UNIXTIME(checkout_time)) = '" . mysql_real_escape_string('2014') . "'
GROUP BY YEAR(FROM_UNIXTIME(checkout_time)) 
, DAY(FROM_UNIXTIME(checkout_time)) 
ORDER BY YEAR(FROM_UNIXTIME(checkout_time)) DESC
 , MONTH(FROM_UNIXTIME(checkout_time)) DESC
, FROM_UNIXTIME(checkout_time) 
";

Open in new window


to get the following output:
YEAR: 2014
MONTH: February
12 Feb, 2014  || ¿660
MONTH: January
10 Jan, 2014  || ¿1000
13 Jan, 2014  || ¿2000

Open in new window


Before, your output resulted in the following:
YEAR: 2014
MONTH: February
01 Jan, 1970  || ¿150
01 Jan, 1970  || ¿10
01 Jan, 1970  || ¿10
01 Jan, 1970  || ¿490
MONTH: January
01 Jan, 1970  || ¿1000
01 Jan, 1970  || ¿2000

Open in new window


Nevertheless, its working flawlessly and I am awarding you the points for finding the right solution. Thank you again for your fast reply. I also learned a great deal form your code.

Regards.
0
 

Author Closing Comment

by:slickip
ID: 39853591
Thank you again. Greatly appreciate it.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now