reformat the outcome of a query

Steynsk
Steynsk used Ask the Experts™
on
Hi experts,

I've got this select query:

SELECT type, date, volume FROM mytable WHERE active='1'

what results in:

Type      Date      volume
1      2011-12-12      13
1      2011-12-13      1
1      2011-12-14      15
1      2011-12-15      13
1      2011-12-16      2
3      2011-12-12      2
3      2011-12-13      6
3      2011-12-14      8
3      2011-12-15      12
3      2011-12-16      6

But I'd like it to become like this:
 wanted format

Is there a simple way to do this in PHP can someone please help me by pointing me in the right direction? Or can this be done in the SQL (Mysql) query ? Mybe a post or artickle that is about this subject..
and how do you call this (English is not my native lanuage) .

Multiple comments are very welcome!

Thanks,

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think it is called Pivot.

You may take a look at http://gonzalo123.wordpress.com/2010/01/24/pivot-tables-in-php/

There is a code and example available there.

Author

Commented:
Thank you for the quick response but I can't figure out how to implement this on my case. Don't you have more simple single table sample?

Thanks,
Commented:
Try this:

<?php

$rows = array(
	array("type"=>"1","date"=>"2011-12-12","volume"=>"13"),
	array("type"=>"1","date"=>"2011-12-13","volume"=>"1"),
	array("type"=>"1","date"=>"2011-12-14","volume"=>"15"),
	array("type"=>"1","date"=>"2011-12-15","volume"=>"13"),
	array("type"=>"1","date"=>"2011-12-16","volume"=>"2"),
	array("type"=>"3","date"=>"2011-12-12","volume"=>"2"),
	array("type"=>"3","date"=>"2011-12-13","volume"=>"6"),
	array("type"=>"3","date"=>"2011-12-14","volume"=>"8"),
	array("type"=>"3","date"=>"2011-12-15","volume"=>"12"),
	array("type"=>"3","date"=>"2011-12-16","volume"=>"6")
);

$out = array();
for($i=0,$n=count($rows);$i<$n;$i++) {
	$type = $rows[$i]["type"];
	$date = $rows[$i]["date"];
	$volume = $rows[$i]["volume"];
	if (!isset($out[$type])) {
		$out[$type] = array();
	}
	$out[$type][$date] = $volume;
}

print_r($out);

?>

Open in new window



Output

Array
(
    [1] => Array
        (
            [2011-12-12] => 13
            [2011-12-13] => 1
            [2011-12-14] => 15
            [2011-12-15] => 13
            [2011-12-16] => 2
        )

    [3] => Array
        (
            [2011-12-12] => 2
            [2011-12-13] => 6
            [2011-12-14] => 8
            [2011-12-15] => 12
            [2011-12-16] => 6
        )

)

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial