class MySQLTableReport Generic reporting class. Given a configuration file, that describes the tables and fields to be searched, and information to connect to a database, take form data and generate an SQL query to run.
The report object is made to address a certain class of reports: Time series data, to aggregate by some arbitrary column and return the SUM/MIN/MAX/AVG/etc of other columns.
Basic usage:
$report = new MySQLTableReport( $datasource, $tables, $report);
// run the query and get the result and header data $result = $report->execute($sql); $columns = $report->get_column_names();
// ... display as desired.
the parameters above have the following forms:
$datasource = array( 'host' => $host, // the mysql server hostname 'port' => $port, // optional port number 'user' => $user, // the user credential 'password' => $pass, // the password for the user 'db' => $db, // the database name );
$tables = array( 'fact_table_name' => 'fact', // table_name => alias 'dimension_table_name' => 'dimension', // table_name => alias )
The aliases used for tables are mostly anything you choose *except* that one table must have the alias 'fact'. This is considered the root table to which any additional tables can be joined. There can be only one fact table, but you can have any number of dimension tables as long as each have unique aliases
$report = array(
// the JOIN clause for any dimension tables. Specify the exact clause used // for each table alias 'join' => array( 'dimension' => "USING (id)", ),
// fields for the tables; defined by the table's alias and not the real table name // this allows the exact same report to be used on tables with the same structure // but different names 'fields' => array( 'fact' => array( 'name' => 'clear|where',
// note that these aren't field names in the table, but we need // a place to put them for the form processor to handle them // the filters here will make sure the data gets added to the right // part of the query, and not the WHERE clause like other fields. 'group' => 'group', 'order' => 'order', 'having' => 'having', 'limit' => 'limit', ),
'dimension' => array( 'date' => 'date_range|clear|where', 'price' => 'ge|clear|where' )
), // end fields
// custom fields are allowed as well 'custom_fields' => array( 'epoch' => 'FROM_UNIXTIME(date)', 'snippet' => 'LEFT(info,15)' ) );
Located in /MySQLTableReport.php (line 95)
create a new instance, pass configuration information describing the datasource and the report tables and fields.
Remove blank strings as values in form fields.
Most cases, the forms you create can have empty fields which mean those conditions should be omitted from the WHERE clause. However, the form will send and empty string. When you want an empty field to be removed from the WHERE clause, pass it through the clear filter first.
'fields' => array( 'dimension' => array( 'hostname' => 'clear|where', // if hostname is blank, do not include it in the query. ) )
look for a range of date values for the given column, and return values to be added to the WHERE clause
This is used when you have a column like "invoice_date" in the report, but what you really want to search for is a range of dates bewteen a given start and end date.
To do that, create form fields with _start and _end added to the name, and pass the column to this processor. It will search for the appropriate form fields and build the range.
<input type="text" name="dimension-invoice_date_start"> <input type="text" name="dimension-invoice_date_end">
The config settings would look like:
'fields' => array( 'dimension' => array( 'invoice_date' => 'date_range|clear|where', ) )
Execute the generated query on the configured databse and return a result handle
define the primary table to select from
apply a "greater than or equal to" operator to a WHERE condition, instead of the default equality matching
By default a configuration section like this would produce equality matching:
'fields' => array( 'dimension' => array( 'price' => 'clear|where', // generates SQL such as: WHERE price = <some value> ) )
If you need a range of values, include the appropriate operator as a filter:
'fields' => array( 'dimension' => array( 'price' => 'clear|ge|where', // generates SQL such as: WHERE price >= <some value> ) )
retuns a list of all column names. These will be exactly the same as the columns returned by the query.
returns a list of custom fields names.
Custom fields are additional columns that can be used in the SELECT clause, but not as WHERE or other conditions. They are defined in the configuration used to create the object
given a table and column, find all the unique values. This is a utility method often used when building dropdown lists on a search form, or getting values for pivot operations.
return the list of form fields defined by the configuration parameters used to construct this object. Field names are prefixed by the table *alias*
so if the configuration section looked like : 'fields' => array( 'fact' => array( 'checksum' => '...', ), 'dimension' => array( 'hostname' => '...', ),
The result would be an array with the values ('fact-checksum', 'dimension-hostname').
These are the form field names that will be checked to build the search parameters.
return an associate array with form_field_name => value for all fields.
return the list of values for a given pivot column
return a urlencoded string of parameters that were used in this report.
returns the list of table names, not the aliases
gets the concrete name of a table for the given alias
select the field names for the report tables from the database.
set the GROUP BY expression
greater than: see documentation for ge()
set the HAVING clause
add a table to the JOIN clause
less than or equal to: see documentation for ge()
like: see documentation for ge()
set the LIMIT clause
less than: see documentation for ge()
not equals: see documentation for ge()
set the ORDER BY clause
preform a pivot on a column. Get the unique list of values
and return them as a conditional aggregate expression to be added to the select clause. Right now only one aggregate type is supported: SUM Also a little black magic is used to get the column name from the synthetic column name needed here. The column in the form and config should be called: pivot-{$column_name} and it's value should be the column to return when the expression is true.
For example, if you have a hostname column with a count of signups, and you want to pivot on the hostname and return the aggregate signups for each host as its own column, then the form field should look like:
<input type="checkbox" name="dimension-pivot-hostname" value="signups" /> Count signups per-host
The report object config would look like:
'fields' => array( 'dimension' => array( 'pivot-hostname' => 'pivot|select', ) )
Then remember to set the unique list of value for this pivot operation:
$report = new MySQLTableReport( ... ); $hosts = $report->get_distinct_values('dimension','hostname'); $report->set_pivot_values('dimension-pivot-hostname', $hosts);
Read all form data and process values. This will be called automatically by query() and execute() methods.
generate the SQL query and return it as a string.
raw_where is an unprocessed string that is added to the WHERE clause
add a column to the select field list
pivot operations require some setup -- this defines the list of values to turn into additional columns when we ask the report to pivot a column.
add a condition to the WHERE clause.
Documentation generated on Mon, 12 Mar 2012 11:50:14 -0700 by phpDocumentor 1.4.4