Creating Cross Tabulation (Pivot) Table from MySQL with PHP

[deltabee]

Hi,

 

Pretty new to PHP and having some troubles creating a pivot style table output, was looking to hopefully get some help.

 

As a simplified example, the data in the DB table looks like this:

 

Site

Date

Amount

Chicago

2011-01-01 05:05:05

200

Chicago

2011-01-02 05:05:05

220

Chicago

2011-02-02 05:05:05

210

New York

2011-01-04 05:05:05

20

New York

2011-01-06 05:05:05

222

New York

2011-02-03 05:05:05

100

 

What i'm hoping to do is group the months along the horizontal (columns) so the output looks something like

 

 

Site

Jan

Feb

Chicago

400

220

New York

242

100

 

I'm sure it's really simple to do but i just can't quite figure it out.

 

so far php code wise i'm grouping by month and outputting to an array that contains the site, month, sum(amount) but then it would seem i need to have some nested type loop to output each month for each unique site? That's the bit that's just way beyond me. 

 

Actual PHP:

 

$trading_q = sprintf("select
  S.SITE AS SITE,
  month(P.CREATED_DATE) AS MONTH,
  sum(P.amount) AS SALES
  from PAYMENT P, SITE S
  WHERE P.CLIENT_SITE_ID = S.ID AND P.CREATED_DATE BETWEEN '%s' and '%s'
  %s GROUP BY SITE, MONTH",
  $date_start,$date_end,$storesql);

 

if ($report_type == "trading") {
  $trading_sales = mysql_query($trading_q);
  if (!$trading_sales) {
    echo "Could not successfully run query \r\n ($trading_q) \r\n from DB: " . mysql_error();
    exit;
  }

  if (mysql_num_rows($trading_sales) == 0) {
    echo "No rows found in print_q, nothing to print so am exiting";
  }
}

 

while ($trading_row = mysql_fetch_assoc ( $trading_sales ))
  {
    while { //This is where i some how need to loop through each month per store
      printf("%s,%s,%s\r\n",$trading_row['SITE'],$trading_row['MONTH'],$trading_row['SALES']);
    }
  }

 

Any and all help really appreciated! Thx

[deltabee]

Would i be better off looking to do this in MySQL rather than PHP?

 

something like a

 

select
sum(if(month(p.date) = 1, p.amount,0)) AS JAN
sum(if(month(p.date) = 2, p.amount,0)) AS FEB
...

 

Is that how this kind of thing would be done?

 

 

[kickstart]

Hi

 

I would code that in php rather than trying to use mysql to provide the data.

 

In php I would loop round something like as follows:-

 

$MonthArrayInit = array('StoreName'=>'','JAN'=>0,'FEB'=>0,'MAR'=>0,'APR'=>0,'MAY'=>0,'JUN'=>0,'JUL'=>0,'AUG'=>0,'SEP'=>0,'OCT'=>0,'NOV'=>0,'DEC'=>0);
$MonthArray = $MonthArrayInit;

$TitleRow = array_keys($MonthArrayInit);
echo implode('\t',$TitleRow).'\r\n';

while ($trading_row = mysql_fetch_assoc ( $trading_sales ))
{
if ($trading_row['SITE'] != $MonthArray['StoreName'])
{
	if ($MonthArray['StoreName'] != '')
	{
		echo implode('\t',$MonthArray).'\r\n';
	}
	$MonthArray = $MonthArrayInit;
	$MonthArray['StoreName'] = $trading_row['SITE'];
}
$MonthArray[$trading_row['MONTH']] += $trading_row['SALES'];
} 
if ($MonthArray['StoreName'] != '')
{
echo implode('\t',$MonthArray).'\r\n';
}

 

Not tested so excuse any typos. That should put out the details in tab separated columns.

 

All the best

 

Keith

[deltabee]

Hi Keith,

 

Thanks - makes perfect sense, i'll try that out.