Solved

GROUP BY For reports

Posted on 2014-02-11
7
216 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 …

778 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