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.
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.
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!HL Hans Looman
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:
You can configure the class to access your database in several ways:
1) Hard-code the database settings in the Lionite_Stats_Db class $_config variable
2) Load database configuration parameters dynamically in the Lionite_Stats_Db 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:
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 -
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');
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.
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
$stats -> where('user_id=5');
You can add multiple
WHERE clauses with multiple calls to the
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
$stats -> join('INNER JOIN users_balance ON users_balance.user_id=users.id);
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.
/** 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();
This class provides support for the 'mysql' and 'mysqli' adapters. You can change the adapter by modifying the $_adpater variable in the Lionite_Stats_Db class. If you want to add support for additional adapters, follow the conventions in the class -
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.
/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:
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:
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:
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
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").