Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

GROUP BY For reports

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
slickip
Asked:
slickip
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
slickipAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
slickipAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
slickipAuthor Commented:
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
 
slickipAuthor Commented:
Thank you again. Greatly appreciate it.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now