Database example
This example is the same as the example number 4, but
it uses a custom function to retrieve the data from the database.
The PHP script
<?php
/**
* Test 28, tests the values returned from
the custom function
*/
/*
* Custom function.
* The function is called many as many rows it returns.
* It returns FALSE to say 'no more rows'.
* The function returns an associative array indexed by the name of the fields
*/
function GetData()
{
return(mysql_fetch_assoc($GLOBALS['result']));
}
require_once('../sptpl.inc');
// require_once('../sptpl_db.php');
// require_once('../sptpl_db_mysql.php');
$t=new sptpl();
$t->LoadTemplate('test8.xml');
$t->SetVar("RepTitle","Product sold from DB");
/*
* Open the connection to the database and executes the query.
* Note: the result handler must be global to allow the function to access to it
*/
mysql_connect('localhost','root');
mysql_select_db('sptpl');
$result=mysql_query("select Year, period, ProdA as pA, ProdB as pB from test1 order by Year, period");
/*
* built the report
*/
$t->run("test28.txt");
mysql_close();
?>
|
Second step, write the configuration file test8.xml
encoding='iso-8859-1' ?>
<template>
<report>
<beginreport>
{$RepTitle}
</beginreport>
<row vpos='relative' rowpos='2'>
Product sold over two year
</row>
<block id='Sold'>
<datasource>
<!-- retrieve data from my function GetData -->
<function>GetData</function>
</datasource>
<counter name='TotalA' field='$pA' />
<counter name='TotalB' field='$pB' />
<counter name='NoItems' autoincrement='y' />
<group key='Year' level='1' name='YearGrouping'>
<row vpos='relative' rowpos='2'>Year: {$Year}</row>
<row />
<counter name='TotalYearA' field='$pA' />
<counter name='TotalYearB' field='$pB' />
<counter name='NoRows' autoincrement='y' />
</group>
<body>
<header>
Period ProdA ProdB
------------------------
</header>
<row> {$period} {$pA} {$pB}</row>
</body>
<endgroup name='YearGrouping'>
<row>
------------------------
Total {$TotalYearA} {$TotalYearB}
N.Rows: {$NoRows}
</row>
</endgroup>
</block>
<row vpos='relative' rowpos='2' />
<closereport>
Product A sold: {$TotalA}
Product B sold: {$TotalB}
Total Items: {$NoItems}
</closereport>
<closepage align='right'>
Sales Dep. - 08/08/2003
</closepage>
</report>
</template>
|
And this is the output from the previous
code
| Product sold from DB
Product sold over two year
Year: 2002
Period ProdA ProdB
------------------------
Q1 10 5
Q2 52 30
Q3 7 12
Q4 20 15
------------------------
Total 89 62
N.Rows: 4
Year: 2003
Period ProdA ProdB
------------------------
Q1 15 12
Q2 26 8
------------------------
Total 41 20
N.Rows: 2
Product A sold: 130
Product B sold: 82
Total Items: 6
Sales Dep. - 08/08/2003
|
