Any good PHP MySQL-compatible reporting frameworks out there?

Question

I'm looking for a web-based reporting framework which is PHP-based and works with MySQL.

Here's my problem (besides being too lazy to program this on my own): I have a large (50k+ rows) table which stores log data for multiple clients. These clients need to be able to sort and search and do all those grand things.

I'd really like something with a decent amount of power behind it, which is why I'm apprehensive about building one myself. This isn't a big enough need to merit putting an exorbitant amount of time into, but it is a necessary function for my clients.

Ideally, I would like some sort of framework which I can either pass data or it get the data itself with a templating engine (so it would do all the presentation). I could get the rendered presentation and drop it into my site.

Something so nice probably doesn't exist, but maybe I'll be lucky.

Answer 1

You may try the KoolReport.

Disclaimer: I am working on this project.

It is a php reporting framework, exactly what you look for. You may download framework through the website, clone project from github or use composer to install: composer require koolphp/koolreport.

After installing, here is a basic example of creating a sale report

index.php: This is bootstrap file

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

SaleByCustomer.php: This file defines data connection and data process

<?php

require_once "koolreport/autoload.php";
use \koolreport\processes\Group;
use \koolreport\processes\Limit;
use \koolreport\processes\Sort;


class SalesByCustomer extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "sales"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=db_sales",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                )
            )
        );
    }

    public function setup()
    {
        $this->src('sales')
        ->query("SELECT customerName,dollar_sales FROM customer_product_dollarsales")
        ->pipe(new Group(array(
            "by"=>"customerName",
            "sum"=>"dollar_sales"
        )))
        ->pipe(new Sort(array(
            "dollar_sales"=>"desc"
        )))
        ->pipe(new Limit(array(10)))
        ->pipe($this->dataStore('sales_by_customer'));
    }
}

SalesByCustomer.view.php: This the view file where you can data visualized

<?php 
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\BarChart;
?>

<div class="text-center">
    <h1>Sales Report</h1>
    <h4>This report shows top 10 sales by customer</h4>
</div>
<hr/>

<?php
    BarChart::create(array(
        "dataStore"=>$this->dataStore('sales_by_customer'),
        "width"=>"100%",
        "height"=>"500px",
        "columns"=>array(
            "customerName"=>array(
                "label"=>"Customer"
            ),
            "dollar_sales"=>array(
                "type"=>"number",
                "label"=>"Amount",
                "prefix"=>"$",
            )
        ),
        "options"=>array(
            "title"=>"Sales By Customer"
        )
    ));
?>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore('sales_by_customer'),
        "columns"=>array(
            "customerName"=>array(
                "label"=>"Customer"
            ),
            "dollar_sales"=>array(
                "type"=>"number",
                "label"=>"Amount",
                "prefix"=>"$",
            )
        ),
    "cssClass"=>array(
        "table"=>"table table-hover table-bordered"
    )
));
?>

And here is the result.

Basically you can get data from many data sources at the same time, pipe them through processes then stored result into data store. The data in data store then will be available in the view to get visualization. The Google Charts is integrated inside framework so you can use right away to create beautiful charts and graphs.

Alright, here are some good links:

1. KoolReport Advanced Examples : See some more good examples
2. Doc - Data Sources: Support MySQL, Oracle, SQLServer, MongoDB, CSV, Microsoft Excel ..
3. Doc - Data Processing: Data analyzing and transformation
4. Doc - Data Visualization: get your data visualize with charts, tables and more.
5. Project on Github.

Hope that helps.

Answer 2

PHPRunner - PHP code generator

There's also this, but I don't know if you want it to be free.

There's also this cross platform reporting tool (which isn't in PHP).

Answer 3

I've found a decent substitute that fits my needs pretty well: a Symfony plugin called laiguExtGridPlugin. It's not a framework, but it using JSON calls to get data and displays it with sorting and pagination. I haven't actually implemented it yet, I'm going to read though the source code tonight to see how to do so--there's very little documentation on the plugin, go figure. I'll end up posting something on my blog once I do implement it.

Update: the laiguExtGridPlugin has been implemented, but it sits ontop of a Javascript library called ext. This library is massive, over 27 megs. That's for the full library. The part that I use is around 100KB. I also use jQuery, so both of those libraries loading (which, thankfully, it's only for one page) is quite unacceptable. I will be switch to a jQuery-base grid system.

I also found a commerically licensed jQuery plugin called jqGrid from Trirand. It's a bit out of my price range at $599 for a one seat license with subscription, source, and priority support or $450 for just a license. However, it does look quite nice and reminds me very much of the newer Msoft Office UIs.

For now the former will do just fine; however, I am going to be looking around for a framework. I may just make one myself.

Answer 4

This can be done simply in Agile Toolkit, which would also integrate jQuery and AJAX.

1.git clone git://github.com/atk4/atk4.git

(alternatively you can download a bundle)

2.config.php:

<?php     
$config["atk"]["base_path"]="./atk4/";
$config["dsn"]='mysql://root:root@localhost/project';
$config['url_postfix']='.php';

3.index.php:

<?php
include'atk4/loader.php';
class MyApp extends ApiFrontend {
    function init(){
        parent::init();
        $this->add('jUI');
        $this->add('BasicAuth')->allow('demo','demo')->check();
        $this->add('Menu',null,'Menu')
            ->addMenuItem('report','index')
            ->addMenuItem('logout');
    }
    function page_index($p){
        $this->dbConnect();

        $f=$p->add('Filter',null,null,array('form_empty'));
        $f->addField('line','name');
        $f->addField('line','surname');
        $f->addSubmit('Search');

        $g=$p->add('Grid');
        $g->setSource('user');
        $g->addColumn('text','gender')->makeSortable();
        $g->addColumn('text','name')->makeSortable();
        $g->addColumn('text','surname')->makeSortable();
        $g->addPaginator(25);

        $f->useDQ($g->dq);
    }
}

$api=new MyApp('myapp');
$api->main();

Features are: pagination, sorting, filtering and you can customize everything. Agile toolkit has extensive documentation and learning book.

You can login with u: demo, p: demo

Answer 5

If you havent already, i'd give Roman's suggestion a try. The agiletoolkit is under 6MB for everything and only loads the parts you need in the page but the example he provided in the code above is all you need to create a grid showing the data from the table.

The setSource line determines which table will be fetched from mysql, in this case the 'user' table

  $g->setSource('user');

and if you need to enforce some limit on the rows returned rather than letting the user filter them e.g. just listing guys, you can add

  $g->addCondition('gender','M');

I think there are also some export options for MVCGrid in the atk4-addons directory so you could possibly add Excel or PDF export of the data in the grid although i havent had time to explore those options myself yet - just noticed export.php in the addons directory.

Answer 6

There are many tools that I believe can help you :

https://mysqlreports.com/mysql-reporting-tools/smart-report-engine/
Reporting framework in PHP, with an easy to follow API that you can call from your code to generate reports for MYSQL.

https://mydbr.com : Can help you converting your SQL quires into professional reports

https://mysqlreports.com : Wizard style interface that can help you create PHP reports for MySQL ( you can so search and sort ) it does support member loggin