Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Generating Reports and Statistics in PHP : Page 3

Discover the PHP libraries that help you generate statistics and reports that analyze data from text files, XML, or relational databases.


advertisement

Using PHPReports Generator for Database Reports
PHPReports is a perfect tool for generating reports that reflect data for a set of dates stored in a relational database. PHPReports works through a combination of PHP classes, XML documents, and XSLT transformations. The combination offers a simple way to query a database, a flexible and intuitive way to develop the report's design, and a professional approach for developing and installing plug-ins for PHPReports.

You can download the latest release of PHPReports and install it (on PHP 5) using the following procedure:

  1. Unzip the downloaded archive in any directory—for example, {$APACHE_HOME}/htdocs/{$app_dir}.
  2. Uncomment the line ;extension=php_xsl.dll in php.ini, by deleting the ";" comment character. Then restart your server. If the extension line doesn't appear in your php.ini file, then you must compile PHP with XML/XSLT support.

In your applications, set the include_path property to point to the /phpreports directory (or set it in php.ini).

Creating a First Report
The simplest way to understand how PHPReports works is to develop an example. First, build the sample database that will feed the report. For this example you will use a database that keeps track of the relative values of USD (dollars) and EUR (euros) as compared to GBP (pounds sterling).

You can create the sample database using the following sequence of SQL statements:

Create the database usdeurgbp:

   create database usdeurgbp;


Create the table currencies:

   create table currencies (
     id int not null auto_increment primary key, 
     currency varchar(4), 
     dayref date, 
     currencyvalue double(4,2));

Finally, insert some records into the Currencies table:

   insert into currencies values (1,"USD",'2008-01-15','34.2');
   insert into currencies values (2,"USD",'2008-02-15','42.4');
   insert into currencies values (3,"USD",'2008-03-15','45.4');
   insert into currencies values (4,"USD",'2008-04-15','25.4');
   insert into currencies values (5,"USD",'2008-05-15','29.1');
   insert into currencies values (6,"USD",'2008-06-15','49.1');
   insert into currencies values (7,"USD",'2008-07-15','52.1');
   insert into currencies values (8,"USD",'2008-08-15','50.7');
   insert into currencies values (9,"USD",'2008-09-15','45.7');
   insert into currencies values (10,"USD",'2008-10-15','35.7');
   insert into currencies values (11,"USD",'2008-11-15','30.7');
   insert into currencies values (12,"USD",'2008-12-15','34.7');
   insert into currencies values (13,"EUR",'2008-01-15','27.9');
   insert into currencies values (14,"EUR",'2008-02-15','41.8');
   insert into currencies values (15,"EUR",'2008-03-15','45.2');
   insert into currencies values (16,"EUR",'2008-04-15','23.7');
   insert into currencies values (17,"EUR",'2008-05-15','26.7');
   insert into currencies values (18,"EUR",'2008-06-15','36.5');
   insert into currencies values (19,"EUR",'2008-07-15','48.5');
   insert into currencies values (20,"EUR",'2008-08-15','28.5');
   insert into currencies values (21,"EUR",'2008-09-15','27.9');
   insert into currencies values (22,"EUR",'2008-10-15','27.1');
   insert into currencies values (23,"EUR",'2008-11-15','47.1');
   insert into currencies values (24,"EUR",'2008-12-15','59.1');

With the database in place, you can create your first report. Suppose that you want to obtain a report of the change in USD between February and October 2008. First, create a report layout, which is an XML template file that looks like Listing 3 (see the file reportXML.xml in the downloadable code):

The main parts of the XML report template in Listing 3 are:

  1. Report layer: This is the external layer of the report; it stores the entire report layout.
  2. Page layer: This layer stores all the elements and attributes for a page of the report. The layer defines borders, sizes, headers, and footers.
  3. Groups layer: This layer is marked by the <GROUPS> element, which may contain one or more <GROUP> elements. Each <GROUP> element contains a <FIELDS> element (which may contain <ROW> and <COL> elements that reflect the SQL columns returned by the report query.
Author's Note: An XML file can have only one document layer, and one page layer (the report may have a lot of pages, but you just have to configure one page layer) and some group layers. Also, notice that each layer has its own header and footer.


Now, here's the PHP code required to make your report work (see the reportPHP.php file in the downloadable code):

   <?php
     // include the PHPReports classes on the PHP path!    
     ini_set("include_path",ini_get("include_path").";C:\Program Files\Apache  
              Group\Apache2\htdocs\php\statisticreport\phpreports");
     
     //import PHPReportMaker class   
     require_once("PHPReportMaker.php");
   
      //SQL query
      $sSQL = "SELECT * FROM currencies WHERE dayref > '2008-02-01' 
               AND dayref < '2008-11-01' AND currency='USD'";
      
      //Create the report maker object
      $oRpt = new PHPReportMaker();
   
      //configure the XML file
      $oRpt->setXML("reportXML.xml");
      
      //configure the database connection
      $oRpt->setUser("root");
      $oRpt->setPassword("");
      $oRpt->setConnection("localhost");
      $oRpt->setDatabaseInterface("mysql");
      $oRpt->setSQL($sSQL);
      $oRpt->setDatabase("usdeurgbp");
      $oRpt->run();
   ?>

As you can see, the PHP code is pretty straightforward. Basically, the code:

    • Imports the PHPReportMaker.php class (the main class of PHPReports tool).
    • Composes the SQL query that will populate the report.
    • Creates an instance of PHPReportMaker.
    • Indicates the location of the report layout file (reportXML.xml)
 
Figure 2. Simple Report: Here's a simple table-based report that shows the change in USD from February to October 2008.
  • Configures the database connection.
  • Calls the run() function to generate the report.

When you run the reportPHP.php page shown above, you will get a report similar to that shown in Figure 2:

Inserting SQL Query Results into the Report Layout
While the ability to define the exact column and field layout is useful, it's also a tedious process, and would force you to change the report layout and the PHP code for every change to your query. Fortunately, you don't need to do that. This section builds the same report as the preceding section, but places the SQL query in the report layout XML document, which offers you more flexibility, because you can create any number of reports without modifying the PHP code. The report layout now looks like this:

   <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
   <REPORT>
      <TITLE>Currency Report</TITLE>
      <BACKGROUND_COLOR>#FFFFFF</BACKGROUND_COLOR>
      <SQL>SELECT * FROM currencies WHERE dayref BETWEEN 
           '2008-02-01' AND '2008-11-01' AND currency='USD'</SQL>
      <CONNECTION>localhost</CONNECTION>
      <INTERFACE>mysql</INTERFACE>
      <DATABASE>usdeurgbp</DATABASE>
      <NO_DATA_MSG>No data was found, check your query</NO_DATA_MSG>
   
      <PAGE BORDER="1" SIZE="25" CELLSPACING="0" CELLPADDING="5" WIDTH="500">
      // no modification from here &#133;

And the PHP code looks like this:

   <?php
     // include the PHPReports classes on the PHP path!    
     ini_set("include_path",ini_get("include_path").";
       C:\Program Files\Apache 
       Group\Apache2\htdocs\php\
       statisticreport\phpreports");
     
     //import PHPReportMaker class   
     require_once("PHPReportMaker.php");
   
     //SQL query
     $sSQL = "SELECT * FROM currencies 
       WHERE dayref > '2008-02-01' 
       AND dayref < '2008-11-01' AND 
       currency='USD'";
      
     //Create the report maker object
     $oRpt = new PHPReportMaker();
   
     //configure the XML file
     $oRpt->setXML("reportXML.xml");
      
     //configure the database connection
     $oRpt->setUser("root");
     $oRpt->setPassword("");  
     $oRpt->run();
   ?>

You've seen a quick-and-dirty overview of generating reports from databases. Fortunately, you're not limited to simple text or table output; you can use CSS to customize the appearance of your reports, or go beyond that to generate graphical charts using SVG.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap