Analytics and Graphing with PHP / SQL

Analytics and Graphing with PHP / SQL

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.

  • Language: PHP
  • Released: Apr 10, 2012
    Last Update: Feb 21, 2014

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.

Features:

  • 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

Requirements:

  • 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:

Hide

Documentation

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:

require_once('path/to/Stats.php');

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)

Usage

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 users_balance.user_id=users.id);

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:

Example:

/** ajax.php example **/

include('path/to/Stats.php');
$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.

Hide

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="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></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">
$(document).ready(function(){
    var options = {ajaxUrl:'ajax.php'}; // Custom options go here
    LioniteStats.init(<?php echo $stats -> getJsonData(); ?>,options);
});
</script>

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.

Stylesheets

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").

You need to log-in or create an account
  • Create an account
  • Log-in
Please use your real name.
Activation link will be sent to this address.
Minimum 8 characters
Enter your password again

Clicking this button confirms you read and agreed to the terms of use and privacy policy.

X

Save your watchlist

Fill your details below to receive project updates from your watch list - including new versions, price changes and discounts.

I agree to the terms of use and privacy policy.

3 licenses, starting from From » $39.99 14 day money-back guarantee View Licenses
or Get a quote

for customization or integration services

  • Good
    JR Jason Raymond
    1 year ago, 0 comments
    Was this helpful?
    Flag 2 of 2 people found this review helpful
  • Really, really great script!!
    I think it saved me more than 60 hours!
    Great work!
    CW Christian Wenzer
    2 years ago, 0 comments
    Was this helpful?
    Flag 2 of 2 people found this review helpful
  • Man this saved me a bunch of time! It was very easy to implement and looks fantastic!
    RL Ralph LoBianco
    2 years ago, 0 comments
    Was this helpful?
    Flag 2 of 2 people found this review helpful
Post a comment

Or enter your name and Email
  • F Fredrik 2 weeks ago
    Hi, I have a database with ID, NAME and TIMESTAMP. I would like to know: 1. Is it possible to show one line for each name (multiple lines in the graph). 2. Is it possible to easy create a simple table showing the statistics instead of a graph?
    • Lionite Developer 2 weeks ago
      Hi Fredrick, 1. It is possible to show one line for each name, by using a COUNT() function on the name field. You'll have to write the SQL yourself though, just follow the examples included in the package. 2. Technically, it is possible - you can get the raw data as an array from the class. You'll need to build up the table yourself from it in PHP.
  • JR Jason Raymond License holderSingle-Site License 1 year ago
    Very nice script!!
  • HL Hans Looman License holderSingle-Site License 2 years ago
    This is a fantastic script to get a 'Google Analytics' type of graph out of time based data in a MySQL database. It does not use flash, so works without plugins or other problems end users may run into with flash (iPad for instance does not support flash) It is easy customizable, but when doing so, make sure you pay attention to the ajax.php file which needs to be modified as well to make date pick changes react the right way. It took me a while to figure that out but once I did, it ran smooth! Great script!
    • Lionite Developer 2 years ago
      Hi Hans, Thanks for kind words! I would really appreciate if you could leave this comment as a review - you can review a component by visiting your download history, or by clicking the 'Review' button on the component page while logged-in. Thanks again!