Page 1 of 1

Showing olap query results' in PHP

Posted: Sun Sep 06, 2009 11:33 am
by fooooof
Hi there
I'm doing report web-system in(Oracle & PHP) using OLAP query's

the problem is >>> How to present the result in friendly view???
Example:
SELECT channel_desc, country_id, SUM(amount_sold) as SALES$
FROM sales, customers, times, channels
WHERE sales.time_id = times.time_id
AND sales.cust_id = customers.cust_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc = '2000-09’
AND country_id IN ('UK', 'US')
GROUP BY CUBE(channel_desc, country_id);

this is database result:

CHANNEL_DESC COUNTRY_ID SALES$
------------------- ---------- ---------------
Direct Sales UK 1,378,126
Direct Sales US 2,835,557
Direct Sales 4,213,683
Internet UK 911,739
Internet US 1,732,240
Internet 2,643,979
UK 2,289,865
US 4,567,797
6,857,662

and I want to show it in this form???
UK US total
Direct Sales 1,378,126 2,835,557 4,213,683
Internet 911,739 1,732,240 2,643,979
2,289,865 4,567,797 6,857,662

Re: Showing olap query results' in PHP

Posted: Sun Sep 06, 2009 6:09 pm
by Darhazer
There were several similar topics, about changing the way data is displayed
You need to put it in suitable struct... I assume you know how to perform the query and to fetch a row from it (I've never worked with oracle and do not know what extension you are using for it)

Code: Select all

$result = query(<your-sql-here>);
$data = array();
while ($row = fetch_row($result)) {
   $data[$row['channel_desc']][$row['country_id']] = $row['SALES$'];
}
So it would look like:

Code: Select all

Array(
    'Direct Sales' => array(
                            'US' => '2,835,557',
                            'UK' => '1,378,126',
                            '' => '4,213,683',
);
And it's easy to output it in the way you want:

Code: Select all

foreach ($data as $channel => $stats) {
   echo '<td>' . $channel . '</td><td>' . $stats['UK'] .'</td><td>' . $stats['US'] .'</td><td>' . $stats[''] .'</td>';
}