Database example

Database example

Here I'll show you an simple example how to build one report connected to some database.

The PHP script

<?php
 /*
* test 4: first complete example, data from MySql
*
* It show how to build a report about the product sold over two year
*/
 require_once('sptpl.inc'); require_once('sptpl_db.php');
 // Start the module
 $t=new sptpl();
 // Parse the configuration file
 $t->LoadTemplate('test4.xml');
 // Set the variable {$RepTitle} used as title for the report
 $t->SetVar("RepTitle","Product sold from DB");
 // Make the report
 $t->run("test4.txt");
?>

Second step, write the configuration file test4.xml

<?xml version='1.0' encoding='iso-8859-1' ?>

<template>
 <report>
  <beginreport>
{$RepTitle}
  </beginreport>
    <row vpos='relative' rowpos='2'>
Product sold over two year
  </row>
   <block id='Sold'>
   <datasource>
    <dbclass>db_mysql</dbclass>
    <sql>select Year, period, ProdA as pA, ProdB as pB from test1 order by Year, period</sql>
    <dbname>sptpl</dbname>
    <dbserver>localhost</dbserver>
    <dbuser>root</dbuser>
    <dbpasswd></dbpasswd>
   </datasource>
   <!-- Set the block counter. The first and the second one
   count the product sold over two year. At the end
   of the block, when all rows returned by the query
   below are retrieved, I'll write the total product
   sold -->
    <counter name='TotalA' field='$pA' />
   <counter name='TotalB' field='$pB' />
   <counter name='NoItems' autoincrement='y' />
   <!-- I want one table for each year, so I use
   the year as grouping key -->
    <group key='Year' level='1' name='YearGrouping'>
    <row vpos='relative' rowpos='2'>Year: {$Year}</row>
    <row />
    <!-- Counter for the year. In this way
    I known how many product is sold during
    each year -->
     <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>
    <!-- When all row for the current year are
    printed, close the table and print the counters
    set for the block -->
    <endgroup name='YearGrouping'>
    <row>
------------------------
Total {$TotalYearA} {$TotalYearB}
N.Rows: {$NoRows}
    </row>
   </endgroup>
  </block>
   <!-- At the end print the total product sold
   during the two years
   <row vpos='relative' rowpos='2' />
  <closereport>
Product A sold: {$TotalA}
Product B sold: {$TotalB}
Total Items: {$NoItems}
  </closereport>
  <closepage>
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