MySQL Report

This report show how to build report from MySQL data

MonthAmount
May, 2005 $4,824
June, 2005 $9,632
July, 2005 $28,374
August, 2005 $24,072
February, 2006 $514

This report shows how to build report from MySQL data. In the settings() we define MySQL data source connection. In the setup() function of report, we input query to query data from MySQL then continue piping data to various processes until it reach the datastore. Data in datastore will be later used to draw chart and table.

<?php 

require_once "SakilaRental.php";
$report = new SakilaRental;
$report->run()->render();
<?php

require_once "../../../../koolreport/autoload.php";

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;

class SakilaRental extends KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "sakila_rental"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=sakila",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    }    
    protected function setup()
    {
        $this->src('sakila_rental')
        ->query("SELECT payment_date,amount FROM payment")
        ->pipe(new TimeBucket(array(
            "payment_date"=>"month"
        )))
        ->pipe(new Group(array(
            "by"=>"payment_date",
            "sum"=>"amount"
        )))
        ->pipe($this->dataStore('sale_by_month'));
    } 
}
<?php 
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\ColumnChart;
?>

<div class="report-content">
    <div class="text-center">
        <h1>MySQL Report</h1>
        <p class="lead">This report show how to build report from MySQL data</p>
    </div>

    <?php
    ColumnChart::create(array(
        "dataStore"=>$this->dataStore('sale_by_month'),  
        "columns"=>array(
            "payment_date"=>array(
                "label"=>"Month",
                "type"=>"datetime",
                "format"=>"Y-n",
                "displayFormat"=>"F, Y",
            ),
            "amount"=>array(
                "label"=>"Amount",
                "type"=>"number",
                "prefix"=>"$",
            )
        ),
        "width"=>"100%",
    ));
    ?>

    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore('sale_by_month'),
        "columns"=>array(
            "payment_date"=>array(
                "label"=>"Month",
                "type"=>"datetime",
                "format"=>"Y-n",
                "displayFormat"=>"F, Y",
            ),
            "amount"=>array(
                "label"=>"Amount",
                "type"=>"number",
                "prefix"=>"$",
                        )
        ),
        "cssClass"=>array(
            "table"=>"table table-hover table-bordered"
        )
    ));
    ?>
</div>
payment_dateamount
2005-05-25 11:30:37 3
2005-05-28 10:35:23 1
2005-06-15 00:54:12 6
2005-06-15 18:02:53 1
2005-06-15 21:08:46 10
2005-06-16 15:18:57 5
2005-06-18 08:41:48 5
2005-06-18 13:33:59 1
2005-06-21 06:24:45 4
2005-07-08 03:17:05 6
2005-07-08 07:33:56 6
2005-07-09 13:24:07 5
2005-07-09 16:38:01 5
2005-07-11 10:13:46 8
2005-07-27 11:31:22 3
2005-07-28 09:04:45 5
2005-07-28 16:18:23 5
2005-07-28 17:33:39 1
2005-07-28 19:20:07 1
2005-07-29 03:58:49 3

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport