Database data providers: constructor and query parameters

The database data providers have the same arguments: the database connection, the SQL query and an optional array of query parameters, that replace the ? placeholders of the query in the same order.

DataProviderMySQLi(mysqli $connection, string $query, array $args = [])
DataProviderPDO(PDO $connection, string $query, array $args = [])
DataProviderEloquent(?ConnectionInterface $connection, string $query, array $args = []) : Laravel connection, eg. DB::connection() (if null, the default connection is used)
DataProviderDoctrine($connectionOrEntityManager, string $query, array $args = []) : Doctrine Connection or EntityManager

DataProviderMySQLi requires the mysqlnd driver (included in the standard PHP distributions). DataProviderPDO, DataProviderEloquent and DataProviderDoctrine count the rows with a COUNT(*) query that contains the report query: write the query without the final semicolon.

Data providers linked to sections and to a datalist

Placeholders in the queries

The query of a section can contain the placeholders of the other sections and the variables, eg. {inv.invoiceNumber}: they are replaced with their current values each time the section starts, before the query is executed. This is how a detail section reads the rows of the current master row.

Warning: the placeholders are replaced as text, they are not query parameters. Use them only with values that cannot contain SQL code (eg. numeric keys read from the database), and use the ? parameters for the values received from the users.

The placeholders are not replaced in the queries of the datalists.

The array and file data providers use the same placeholders in their filter (DataProviderArray, DataProviderCSV, DataProviderJSON) or in their XPath expression (DataProviderXML).

Data from a PHP array (DataProviderArray)

DataProviderArray(array $rows, string $filter = '')

Each row is an associative array (field name => value). The optional filter links a detail section to its master section: field=value, more conditions joined by AND, eg. orderNumber={ord.orderNumber} AND type=A. The placeholders are replaced each time the section starts, as in the SQL queries. The values can be enclosed in quotes; two numeric values are compared as numbers (10 = 10.00), the other values as strings. An empty filter returns all the rows.

DataProviderArray is also the base class of the file data providers and of the custom data providers (see below).

Master / detail report from PHP arrays

CSV files (DataProviderCSV)

DataProviderCSV(string $file, string $filter = '', array $options = [])

Each line of the file is a data row; the first line contains the field names. The filter is the same of DataProviderArray. The file is read once, when the data provider is created; a relative path is resolved from the current working directory, as the template file.

Options:
delimiter Field separator: , ; tab | (default: detected from the first line)
enclosure Field enclosure character (default ")
encoding Encoding of the file, converted to UTF-8 (default UTF-8). A CSV saved by Excel on Windows is usually Windows-1252: without this option the accented letters are not printed correctly.
header true (default): the first line contains the field names; false: the fields are named col1, col2, ..
columns Field names (array), they replace the names of the first line
decimalComma true: the numbers with decimal comma (eg. 1.234,56 or 12,5) are converted to 1234.56 and 12.5, so the format masks (eg. C2) and the filters work. Without this option 12,50 is read as 12.
trim true (default): removes the spaces at the beginning and at the end of the values

The UTF-8 BOM is removed. Fields enclosed in quotes can contain the delimiter and line breaks.

PHP code

The fields of the CSV file are used in the template as the fields of a database table, eg. {prod.description} and {prod.price}. A complete example is the Price list template.

price-list.csv

JSON files (DataProviderJSON)

DataProviderJSON(string $file, string $filter = '', array $options = [])

The data rows are an array of objects. The filter is the same of DataProviderArray. The path option selects the array inside the JSON document, with the keys separated by dots, eg. data.orders (default: the document is the array). A single object is a single data row.

The fields used in the template must be simple values: a nested object or array is converted to its JSON text.

orders.json

PHP code

XML files (DataProviderXML)

DataProviderXML(string $file, string $xpath, array $options = [])

The data rows are the XML elements selected by an XPath expression. The XPath can contain placeholders, replaced each time the section starts: so the detail section can select the elements nested in the current master element, eg. /orders/order[@number="{ord.number}"]/line.

Fields of a data row (selected element):
- the attributes of the element
- the child elements that contain only text (the first one when a name is repeated; a child element with the same name of an attribute replaces it)
- value: the text of the element, when it has no child elements

The namespaces option registers the prefixes used in the XPath (array prefix => namespace URI). External entities and DTDs are not loaded.

orders.xml (fields of an order: number, customer, date; fields of a line: code, qty, description, amount)

PHP code

Custom data providers

The simplest way to read the data from another source (eg. a REST API or another file format) is a class that extends DataProviderArray: it reads the rows in the constructor, while the filter and the other methods are inherited.

A data provider can also be written from scratch: it is a class that implements the DataProviderInterface interface:

execute() Reads the data
fetchNext() Returns the next row as an associative array (field name => value), or null at the end of the data
getCurrentRow() Returns the current row
hasMoreRecords() Returns true if the current row is not null
getRecordCount() Returns the number of rows
reset() Resets the data provider
getQuery() / setQuery() Query with the placeholders already replaced
getQueryRaw() / setQueryRaw() Original query, with the placeholders

Data provider that reads a REST API