Analytics and Graphing with PHP / SQL

Analytics and Graphing with PHP / SQL

Released 4 years ago , Last update 2 years ago

Add a Google Analytics inspired interface to any table in your MySQL (or other SQL based) database. Includes time period and date range controls and complete usage examples.

  • 5 4
  • 4 1
  • 3 0
  • 2 1
  • 1 0
6 Reviews Read Reviews

Adding statistics and graphs to a website / web application is a time consuming and often complicated process - composing the database queries, processing the data and developing an interface to control it are all major time sinks.

This component condenses years of experience implementing such interfaces into an easy to integrate PHP class and styled HTML / JS controls.


  • Plug-and-play - Just pass the database table name, time column and data column(s) to the PHP class and let it handle querying and processing.
  • Data grouping by multiple time periods - by day, by week, by month and by year. New in ver. 1.2: Grouping by hour.
  • Time range control for specifying date range dynamically.
  • Graphs styles are inspired by Google Analytics.
  • Update data asynchronously via AJAX


  • PHP 5+
  • MySQL database 5+ (can be adjusted to use other SQL databases, see below)
  • Tables with time based data (rows are stamped with a date, datetime or timestamp values)

Included libraries:


14 day 14-day money-back guarantee


Single-Site License

  • Perpetual license

  • 1 site, unlimited servers

  • No distribution (hosted use only)

  • Commercial use

  • 6 months support


Developer License

  • Perpetual license

  • 3 projects

  • Can distribute code and binary products

  • Commercial use

  • 6 months support


Setup and configuration

The library files are located in /library/Lionite/. Place it with your other library / PHP files, and include it in the page you want to show the graph:


Connecting to the database

You can configure the class to access your database in several ways:

1) Hard-code the database settings in the LioniteStatsDb class $_config variable

2) Load database configuration parameters dynamically in the LioniteStatsDb constructor and pass them to the config() method.

3) Configure the database at run-time by calling the db() method with the connection parameters:

$stats -> db(array('database' => 'mydb','username' => 'myuser','password' => 'mypass'));

(This replaces the 'connect()' method used in previous versions)


In order to generate graph data using the PHP library, we need to pass it several required parameters:

  • The name of the table we want to analyze
  • The name of the date/time column (a TIMESTAMP, DATE or DATETIME column type)
  • The name of the data column(s) we want to use in our graph, along with an aggregation function such as COUNT(), SUM() or AVG().

We can pass those parameters in the constructor of the class, or via setter methods.

Example using constructor:

$stats = new Lionite_Stats(array(
    'table' => 'views',
    'timeCol' => 'date',
    'dataCols' => array('Views' => 'SUM(views)')

Example using setter methods (supports chaining):

$stats = new Lionite_Stats();
$stats -> setTable('views')
       -> setTimeColumn('date')
       -> setDataColumns(array('Views' => 'SUM(views)'));

Once we call the processData() method, the table will be queried and the data will be processed.

$stats -> processData();

That's basically all we need to generate the data for the graphs, but we can control a few more aspects of the data:

Choose a date period to group the data by. Possible values include 'hourly','daily','weekly','monthy','yearly'

$stats -> setPeriod('weekly');

Set the date range for the data. The class will provide some useful defaults if no date range is given -

  • 1 day ago for the 'hourly' period
  • 1 month ago from today for the 'daily' period
  • 12 weeks ago from today for the 'weekly' period
  • 1 year ago from today for the 'monthly' period
  • 5 years ago from today for the 'yearly' period

Setting the data range ourselves is done via the setDateRange() method. It accepts a string of two yyyy-mm-dd dates separated with a hyphen:

$stats -> setDateRange('2012-04-10 - 2012-02-10');

MySQL Aggregate functions

Aggregating data for each period ('daily','weekly', etc) is done using MySQL aggregate functions. If you have no previous experience with those, we'll cover the most commonly used ones briefly here:

  • COUNT(column) - COUNT() counts the non-null values of a column. It is often used to count the primary key of a table (in which case we are basically counting rows), but can be used on any column.
  • SUM(column) - SUM() sums the values of the column it is applied on. You would use SUM() to sum up columns that represent amounts - such as items in inventory or cash balance.
  • AVG(column) - AVG() returns the AVG() amount of the column it is applied on. You would use AVG() on columns that you would use SUM() on - but to get the average value instead of the sum of the values.

For more detailed information and additional aggregate functions, see the MySQL manual page on the subject.

Filter conditions and advanced usage (WHERE and JOIN)

We might need to filter the analyzed data based on some criteria. For example, we want to show each user only his specific data. For that purpose we can add WHERE clauses using the where() method:

$stats -> where('user_id=5');

You can add multiple WHERE clauses with multiple calls to the where() method.

Note: this class does not make any pretense to escape or filter your data. If you pass user input or from an otherwise untrusted source into it, make sure you escape it properly.

In addition, you can join multiple tables in order to aggregate columns that are not on the main table passed to the class. Joining tables is done by passing the join condition to the join() method:

$stats -> join('INNER JOIN users_balance ON;

AJAX script

In addition to the setup in our main page, we will need an additional script that will be the source of the AJAX requests from the graph controls when changing the time period and changing the date range. Note: the AJAX script is optional - if you do not wish for the user to be able to change the time period or date range, you can skip this part. Read more about the graph controls on the bottom of the "Showing the graphs" section below.

This script should initialize the PHP class the same way we've just covered, and output the generated JSON directly instead of passing it to the Javascript class:


/** ajax.php example **/

$stats = new Lionite_Stats();
$stats -> setTable('views')
       -> setTimeColumn('date')
       -> setDataColumns(array('Views' => 'SUM(views)'))
       -> processData();
echo $stats -> getJsonData();

Additional database adapters

This class provides support for the 'mysql' and 'mysqli' adapters. You can change the adapter by modifying the $adpater variable in the LioniteStats_Db class. If you want to add support for additional adapters, follow the conventions in the class -

  • Add a connection method prefixed with the adapter name (example: mysqlConnect())
  • Add a query method prefixed with the adapter name (example: mysqlQuery())
  • Add the adapter to the escape() method (see method for example)

Inline PHP documentation

The class is fully documented with explanations on the various parameters that can be used in each method. In addition, a usage example is included with the component.

Showing the graphs

First we need to include the provided assets - Javascript and CSS scripts that are found in the /assets directory. We recommend that you copy the /assets directory as is for easy update in the future. Place the /assets directory under your document root folder (ie, in a publicly accessible folder), and include the following scripts in the <head> tag of your document:

<script type="text/javascript" src="assets/js/required.js"></script>
<script type="text/javascript" src="assets/js/stats.js"></script>

The required.js script includes a minified version of moment.js, datepicker.js and jquery.flot.min.js. All three scripts are included in the package as well, and you can download the originals from their respective websites if needed. stats.js was left separated in case you need to customize it (see below).

In addition, you need to include jQuery if it is not already present. We usually include it via the Google CDN:

<script type="text/javascript" src="//"></script>

In order to initialize the graphs, we call the init() method on the LioniteStats object and pass it the data generated by the PHP class and additional options as needed:

<script type="text/javascript">
    var options = {ajaxUrl:'ajax.php'}; // Custom options go here
    LioniteStats.init(<?php echo $stats -> getJsonData(); ?>,options);

Options include:

  • ajaxUrl - A path to the PHP script that would handle AJAX calls (as explained in the PHP section)
  • template - The graph HTML template.
  • colors - An array of hex colors to be used by the graph line series.


Next we include the provided stylesheets.

<link href="assets/css/reset.css" rel="stylesheet" type="text/css" />
<link href="assets/css/stats.css" rel="stylesheet" type="text/css" />
<link href="assets/css/datepicker.css" rel="stylesheet" type="text/css" />

The package includes a CSS reset script taken from the HTML 5 boilerplate - if you already use your own CSS reset or don't feel the need to use one, you can skip it.

You can customize the appearance of the graph controls and hover bubble in assets/css/stats.css and the appearance of the range datepicker in assets/css/datepicker.css.

Graph controls

The included example includes graph controls for changing the time period and date range. If you wish for graph controls to appear for your graphs, you need copy it as-is from the example (the form with id="stats-controls").

3 licenses, starting from From » $39.99 View Licenses

Get A Quote

What do you need?
  • Custom development
  • Integration
  • Customization / Reskinning
  • Consultation
When do you need it?
  • Soon
  • Next week
  • Next month
  • Anytime

Thanks for getting in touch!

Your quote details have been received and we'll get back to you soon.

Or enter your name and Email
  • V Vlad License holderSingle-Site License
    6 month support
    Purchased on Feb 21, 2016
    4 months ago
    Hi. I am interested in this but I have some doubts seeing it wasn't updated since 2 years ago. Can you shed some light on this? Thanks
    • Lionite Publisher 4 months ago
      Hi Vlad, It hasn't been updated but it's still maintained. The last known bug was reported and fixed exactly 2 years and 4 days ago, ever since no new bugs have been found. Functionality wise it's the same, we don't have any plans of adding more stuff to it. Hope this helps!
  • HK Hari Krishna 5 months ago
    Hi i am interested to buy this plugin, what type of graphs you will provide and could you send total screen shots for this plugin.
    • Lionite Publisher 5 months ago
      Hari, I've updated the product page above with larger screenshots. Take a look and see if it's what you're looking for. The graph type used by the plugin is a a line-area graph. The plugin uses the Flot library for rendering the data (, so it can technically support all the graphs supported by it, but it will require some modification to the code.
    • HK Hari Krishna 5 months ago
      Is there any discount for us?
    • Lionite Publisher 5 months ago
      I'm afraid we aren't running any promotions right now.
  • BN Bill Noble License holderSingle-Site License
    6 month support
    Purchased on Jan 10, 2015
    2 years ago
    I am interested on using your package with CodeIgniter. Are there any database integration issues I should be aware of? Or any other compatibility issues?
    • Lionite Publisher 2 years ago
      Hi Bill, The code is framework independent, so it should work with any framework. It is built for MySQL, and while it's possible it would work with other databases, I can't guarantee it.
    • 2 years ago
      Are you able to offer a money back guarantee if I can't get it to work well with CodeIgniter? I am using MySQL so that shouldn't be a problem.
    • Lionite Publisher 2 years ago
      Sure Bill, if it doesn't work out for you, we'll provide you with a refund.