1. Introduction

PHP Analytics is a WordPress plugin that extends your blog platform’s functionalities by adding the chance to define connections to the database, execute queries and build views over the returned resultset (mainly tables and charts).

The full process to create a report could be resume as follows:
1. Create a Connection
2. Create a query
3. Create a view
4. Publish
5. Variables

2. Assumptions

From now on we will consider the following assumption:

• You have installed PHP Analytics in your WordPress platform as explained in the Installation Guide.
• You have all the informations you need to connect to the database(s)
• The database(s) you want to build your report on is available
• You have knowledge about relational databases and SQL query (see official documentation)
• You know the WordPress platform and you are comfortable in create/modify/delete a WordPress page/post content.

3. Create a Connection

In the “Connections” page, it’s possible to define all the parameters to connect to a MySQL database (other databases are not currently supported).

By default, the page display a list of the created queries. For each created query a set of options is available:

• Edit: modify the current connection
• Test: test the current connection
• Drop: delete the current connection (also available as global action)

To create a connection you are required to provide the following parameters:

• Name: the MySQL database name.
• Host: the hostname/ip address (and eventually the port if different from the default one) where MySQL is installed (e.g. mydomain.com, mydomain.com:4444, localhost, 192.168.0.10, etc…).
• Username: the username to oconnect to the MySQL database.
• Password: the username’s relative password.

Before saving the connection’s metadata please check the connection itself by clicking on the Testbutton; be aware that the testing utility will not check the database name parameter.

4. Create a query

In the “Queries” page it’s possible to define the query to get the result set we will build our views on.

By default, the page display a list of the created queries. For each created query a set of options is available:

• Edit: modify the current query
• Copy: duplicate the current query
• Test: test the current query
• Drop: delete the current query (also available as global action).

To create a new query you will be asked to provide the following information:

• Connection: one of the database connections you have defined in the Connections page (see previous chapter).
• Query Name: an identifier for the query.
• SQL Statement: the SQL query (it has to be a select statement).

It’s recommended to test the query by clicking on the Test button before proceed; the testing utility will show you the first 20 records.

5. Create a View

In the Analytics menu you will find two more links Tables and Charts. Both of them will redirect you to the view (Table or Chart) creation page.

By default, the creation pages display a list of the created views. For each created view a set of options is available:

• Edit: modify the current view
• Copy: duplicate the current view
• Test: test the current view
• Drop: delete the current view (also available as global action).

Tables and Charts have a common parameter you need to initialize before creating the view itself:

• Query: one of the created queries (see previous chapter).

Once you have choosen the desired query, just click on the side button to start the creation process.

5.1 Tables

You can choose to customize the table as you prefer by adding optionally title, header, footer, results navigator and customizing the column headers.
A set of predefined CSS styles is also available and can be modified; is it also possible to add new ones, by copying them in the styles folder (see the Administration Guide).

Below you can find the table view’s properties you can customize:

• Table Name: an identifier for the view
• Table Title: a title for the table that will be displayed on top of the column headers.
• CSS Class: a CSS stylesheet that will be assigned to the table. You can choose between a set of default stylesheet. To add a new stylesheet please refer to the Administration Guide.
• HTML Values: if you select this option the browser will parse HTML code whenever the values displayed by the table view are in HTML format.
• Header: show/hide column headers
• Footer: show/hide column footers
• Rows Per Page: define the number of rows you want to display; this is useful when your query returns a uge amount of rows.
• Visible: show/hide a column
• Rename To: change the default SQL query column header

It’s recommended to test te query by clicking on the Test button before saving; remember that if your table view is paginated, you cannot navigate the resultset in this phase.

5.2 Charts

There are several options that can be customized as dimension, title, axis’ labels and values…
Different types of chart are also available: Bar Charts, Stacked Bar Charts, Line Charts, Area Charts, Pie Charts and Radar Charts.

Below you can find the chart view’s properties you can customize:

• Chart Name: an identifier for the view
• Chart Title: a title for the chart that will be displayed on top of it.
• Chart Title Size: chart title’s font size.
• Chart Title Color: chart title’s font color.
• Chart Type: The type of chart you want to use for your chart view. Please refer to the official guide from Open Flash Charts.
• Backgorund: chart background color.
• Width: Size in pixel for your chart width. If you want this value to be treated as percentage, please check the %.
• Height: Size in pixel for your chart height. If you want this value to be treated as percentage, please check the %.
  
• Label: by marking this checkbox you specify the related column to be treated as a label for your chart X axis.
• Value: by marking this checkbox you specify the related column to be treated as a value for your chart Y axis (it has to be a number).
• Color: the color to be assigned to the related column value (for Pie Chart type a default color sequence is used; please refer to the Administration Guide if you want to modify it).
• Rename To: change the default column name that is used in the chart legend.

You can also define the following chart’s axis properties:

• Axis Color: the color of the axis line
• Axis Thickness: the size of the axis line
• Axis Grid Color: the color of the related axis grid
• Axis Grid Lines (%): define the percentage of the grid line density.
• Labels Color: the font color of the axis labels
• Labels Size: the font size of the axis labels
• Labels Rotation: the axis label rotation angle (only for X axis).
• Legend: a main axis label
• Labels Color: the font color of the main axis label
• Labels Size: the font size of the main axis label

Remember that if the chart is a Pie Chart, axis properties are not considered.
For Radar Chart, X axis properties are not considered either.

It’s recommended to test the query by clicking on the Test button before saving.

6. Publish

Once done with the views definition, we can create a WordPress page/post and add our views.
In the TinyMCE editor 2 buttons are available to allow you to choose the view you want to be published.

Click on the button of the related view you want to publish; a dialog box will appear to let you choose the created view.

Mark the iFrame checkbox if you want the current view to be display within an iFrame.

By selecting the view,the metacode definition of the view itself will be created and the plugin engine will transform it in the correspondent HTML code to be embedded in the page.

6.1 PHP Analytics metacode

Let’s take a look to the PHP Analytics metacode that is created automatically when you choose a view to be published.

The metacode requires 3 parameters:

• id: the id of the view to be published (database table’s primary key; please refer to the Administration Guide for more informations).
• type: can be chart or table.
• iframe: can be y or n (y: view is published within an iFrame. n: no iFrame).

You can write the metacode manually, but you need to make sure the parameters you are specifing are ok.

7. Variables

Variables are keywords that can be used in your queries by using the following syntax: $variable_name

At the time of the query execution, every variable that has been previously defined in the administration tool, is replaced with the values passed trough a GET or a POST method; if none of them is used, a default value (specified at the variable definition phase) is used.

Usually a variable is included in the where clause so that you can filter dynamically your resultset. A typical example could be a set of charts that describe the user activity within the user’s profile page. Instead of creating a number of queries for each user (e.g.” select … from users_table where user_id = 1“), we can create a single query that works for all the users by simply passing to the query itself the user_id value, using a GET or a POST method (e.g.” select … from users_table where user_id = $user_id“).

So, if you use the GET method, the user’s profile page URL will look as follows: www.mydomain.com/profile.php?user_id=1
If you use the POST method, you will probably use in the form the POST method is associated to as follows: <input type=”hidden” id=”user_id” name=”user_id” value=”1″ />

In the example above we have used a hidden field type; but we can use whatever field type we wish.

8. Recommendations

Even if PHP Analytics could seem pretty easy to use, there are a couple of thing you need to be aware.

7.1 Delete a connection

If you modify or delete a connection you have to make sure it won’t affect any published view that has been built on a query that refers to the connection itself.

7.2 Modify or Delete a query

If you delete a query or you modify the column order and datatypes of the related result set, all the related views could not work properly anymore.